Wath is the last day of this PERIODO?

[2009luca]

I have MyVar as string.

Assuming MyVar="1 QUAD 2016"
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 QUAD 2016
1 SEM 2016
1 TRIM 2016
2 QUAD 2016
2 SEM 2016
2 TRIM 2016
3 QUAD 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

 

[Hypetia]

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.
___

[pre]
Private Function LastDay(MyVar As String) As Date
Dim V() As String
V = Split(UCase$(Trim$(MyVar)))
Select Case V(1)
Case "TRIM", "QUAD", "SEM"
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[/pre]
___

Following output was produced with this function.
[pre]
1 QUAD 2016 30/04/2016
1 SEM 2016 30/06/2016
1 TRIM 2016 31/03/2016
2 QUAD 2016 31/08/2016
2 SEM 2016 31/12/2016
2 TRIM 2016 30/06/2016
3 QUAD 2016 31/12/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 [/pre]

 

[strongm]

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

[blue]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" [green]' ann just a place holder[/green]
    [green]'Periods = "ann,sem,qua,tri" 'if using non-traditional definitions of trimester and quadrimester[/green]
    If Val(strDate) = 0 Then
        myMonth = (InStr(Months, Left(LCase(strDate), 3)) + 3) / 4
        If myMonth = 13 Then myMonth = 12 [green]'deal with Anno[/green]
    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[/blue]

 

[strongm]

Did either of the above posts help?

 

[2009luca]

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