×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Wath is the last day of this PERIODO?

Wath is the last day of this PERIODO?

(OP)
I have MyVar as string.

In this case LastDay=30/04/2016

Assuming MyVar="1 TRIM 2016"
In this case LastDay=31/03/2016

Assuming MyVar="OTT 2016"
In this case LastDay=31/10/2016

Ecc...

How to return the LastDAy (as date dimensioned) with a function or other code?

Note:
the Year is dinamic the name of period are stable

list of possible value can have MyVar
1 SEM 2016
1 TRIM 2016
2 SEM 2016
2 TRIM 2016
3 TRIM 2016
4 TRIM 2016
AGO 2016
ANNO 2016
APR 2016
DIC 2016
FEB 2016
GEN 2016
GIU 2016
LUG 2016
MAG 2016
MAR 2016
NOV 2016
OTT 2016
SET 2016

RE: Wath is the last day of this PERIODO?

Considering the fact that month names are in Italian, the code will be able to recognize them correctly, if regional settings are set to Italian, which I assume you have.

Try the following function.
___

Private Function LastDay(MyVar As String) As Date
Dim V() As String
V = Split(UCase$(Trim$(MyVar)))
Select Case V(1)
LastDay = DateSerial(V(2), Val(Split("TRIM 3, QUAD 4, SEM 6", V(1))(1)) * V(0) + 1, 0)
Case Else
If V(0) = "ANNO" Then
LastDay = DateSerial(V(1) + 1, 1, 1) - 1
Else
LastDay = DateAdd("m", 1, CDate(MyVar)) - 1
End If
End Select
End Function 
___

Following output was produced with this function.
1 QUAD 2016   30/04/2016
1 SEM 2016    30/06/2016
1 TRIM 2016   31/03/2016
2 SEM 2016    31/12/2016
2 TRIM 2016   30/06/2016
3 TRIM 2016   30/09/2016
4 TRIM 2016   31/12/2016
AGO 2016      31/08/2016
ANNO 2016     31/12/2016
APR 2016      30/04/2016
DIC 2016      31/12/2016
FEB 2016      29/02/2016
GEN 2016      31/01/2016
GIU 2016      30/06/2016
LUG 2016      31/07/2016
MAG 2016      31/05/2016
MAR 2016      31/03/2016
NOV 2016      30/11/2016
OTT 2016      31/10/2016
SET 2016      30/09/2016  

RE: Wath is the last day of this PERIODO?

If, however, your regional settings are not Italian ... the example below may be useful.

Also, are you sure about the last day for QUAD and TRIM? It seems to me that QUAD, TRIM, and SEM represent Quadrimester, Trimester and Semester (but applied to a real year, rather than the academic year) - in which case your examples in the OP for QUAD and TRIM are the wrong way around. The code below uses the definition I think is meant - but if the OP is actually correct, just use the commented out 'non-traditional' line

CODE

Public Function LastDay(strDate As String) As Date
Dim Months As String
Dim myMonth As Long
Dim Periods As String

Months = "gen,feb,mar,apr,mag,giu,lug,ago,set,ott,nov,dic,ann"
Periods = "ann,sem,tri,qua" ' ann just a place holder
If Val(strDate) = 0 Then
myMonth = (InStr(Months, Left(LCase(strDate), 3)) + 3) / 4
If myMonth = 13 Then myMonth = 12 'deal with Anno
Else
myMonth = ((InStr(Periods, Mid(LCase(strDate), 3, 2)) + 3) / 4) * Val(strDate)
End If
LastDay = DateSerial(Right(strDate, 4), myMonth + 1, 0)
End Function 

RE: Wath is the last day of this PERIODO?

Did either of the above posts help?

RE: Wath is the last day of this PERIODO?

(OP)
SORRY for delay but very busy...
all 2 code work perfect, in other case i use stronggm code.
tks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!