INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

Jobs from Indeed

Wath is the last day of this PERIODO?

Wath is the last day of this PERIODO?

(OP)
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

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

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

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
    'Periods = "ann,sem,qua,tri" 'if using non-traditional definitions of trimester and quadrimester
    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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close