Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    If commands with dates

    I have to write some formulas using date as the condition to be greater than or less than. How can I achieve this? I tried =if(05/15/05 >=06/30/05,12,11) but it's only giveng me a 12 no matter what the first date is.

    The second item is I want to calculate the date back three (3) years from a specific date, accounting for leap years.

    The third item is calculating the # of months between two dates and if the one date if before the 16th of the month then the last month is not counted but if the date is after the 15th of the month then the date is counted in the calculation.


    I would appreciate any insight into the three date calculations.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Use either
    datevalue("05/15/05")

    or
    DATE(2005,5,15)

    depending on what you prefer. The way you are doing it, you are doing math (5 divided by 15 divided by 5 compared to 6 divided by 30 divided by 5)

    Steve

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    To find the interval between 2 dates, use the DateDif function:
    Cell reference: =DateDif(A1,B1, inverval)
    Date Values: =DATEDIF("5/5/2002", "7/7/2003", interval) where interval is "d", "m", or "y"

    =DATEDIF("5/5/2002", "7/7/2003","d") returns 428
    =DATEDIF("5/5/2002", "7/7/2003","m") returns 14
    =DATEDIF("5/5/2002", "7/7/2003","y") returns 1

    The first date in the formula must be the earlier date of the two. If this is not controllable, then a conditional statement will make sure the earlier date is the first date in the formula:

    =IF(A1>B1, DATEDIF(B1,A1,"m"),DATEDIF(A1,B1,"m"))


    HTH,
    Maud
    Last edited by Maudibe; 2014-01-22 at 08:18.

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I received some good solutions for general date calculation, however, I also need to calculate the # of months between two dates and if the oldest date is before the 15th of the month then it is counted as one month but if the oldest date is after the 15th of the month it is not counted and if the latest date is before the 16th of the month then the last month is not counted but if the date is after the 15th of the month then the latest month is counted as a complete month.

    any help on this little twist??

    Thanks

  5. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,162
    Thanks
    47
    Thanked 976 Times in 906 Posts
    Dates are just a format of a number where 1 = one day, so knowing the number of days between 2 dates allows you to divide by 30 for an approximation of months. To do it accurately you need a table of days in the months and leap years, then add them up based on the dates you are using.

    cheers, Paul

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Datedif should work, but I am not entirely clear of your "rules". Could you elaborate and perhaps post an example sheet with some variants and what number you are after and we can work on a formula?

    Steve

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,162
    Thanks
    47
    Thanked 976 Times in 906 Posts
    Ah! I see DATEDIF has an interval variable to allow you to return days, months or years - at least it does in Libre Office.

    cheers, Paul

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It can do years ("y"), months ("m"), days ("d"). It can also do months excluding the years ("ym"), days excluding the years ("yd"), and days excluding the years and months ("md"). The last 3 are useful for giving a full year, month, and day description:

    Code:
    =DATEDIF(A1,TODAY(),"y")&" years " & DATEDIF(A1,TODAY(),"ym") & " months " & DATEDIF(A1,TODAY(),"md") & " days"
    Steve

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    MNN

    This UDF might meet your needs
    enter the formula =DateMonths(A1,B1) then copy down

    HTH,
    Maud

    Code:
    Public Function DateMonths(date1 As Date, date2 As Date) As Integer
    Application.Volatile
    DayDate1 = Day(date1)
    DayDate2 = Day(date2)
        If DayDate1 < 15 And DayDate2 >= 15 Then
            DateMonths = DateDiff("m", date1, date2)
        ElseIf DayDate1 < 15 And DayDate2 < 15 Then
            DateMonths = DateDiff("m", date1, date2) - 1
        ElseIf DayDate1 >= 15 And DayDate2 >= 15 Then
            DateMonths = DateDiff("m", date1, date2) - 1
        ElseIf DayDate1 >= 15 And DayDate2 < 15 Then
            DateMonths = IIf(DateDiff("m", date1, date2) - 2 < 0, 0, DateDiff("m", date1, date2) - 2)
        End If
    End Function
    dates1.png

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    MNN,

    Building on Maud's fine work here's a slightly simpler (from a logic point of view or at least my view of logic ) version.
    Code:
    Option Explicit
    
    Public Function iDateMonths(date1 As Date, date2 As Date) As Integer
    
      Dim iDayDate1 As Integer
      Dim iDayDate2 As Integer
      
      Application.Volatile
      iDayDate1 = Day(date1)
      iDayDate2 = Day(date2)
      
      If iDayDate1 < 15 Then
        iDateMonths = DateDiff("m", date1, date2) - IIf(iDayDate2 < 15, 1, 0)
      Else      '*** iDayDate1 >= 15 Then
          iDateMonths = DateDiff("m", date1, date2) - IIf(iDayDate2 >= 15, 1, 2)
      End If
      
    End Function   'iDateMonths
    Note: the Variable typing (preceeding the interger values with an "i") will require changing the calls in your workbook to match.

    HTH
    Last edited by RetiredGeek; 2014-02-10 at 13:47.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Nicely done RG!

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you want even simpler, some boolean logic can simplify it greatly:
    Code:
    Public Function iDateMon(date1 As Date, date2 As Date) As Integer
      Application.Volatile
      iDateMon = DateDiff("m", date1, date2) + (Day(date1) >= 15) + (Day(date2) < 15)
    End Function
    You do the date diff, if the day of date 1 >=15 subtract 1, if the day of date2 <15 subtract 1... [Note TRUE in VB = -1, FALSE = 0]

    Steve

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Steve,

    Fantastic!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Of course you can just use a normal worksheet function:
    =DATEDIF(A1,B1,"m")+(DAY(A1)>DAY(B1))-(DAY(A1) >= 15) - (DAY(B1) < 15)

    The pluses become negatives since in Excel TRUE = +1 (FALSE is still 0). The "(DAY(A1)>DAY(B1))" comes about because the differences between VB's DateDiff and Excel's DateDif function. DateDiff will add a month that excel will not add. Excel only calculates for a complete month, while DateDiff will calculate a full month for a partial month as long as the month has changed [For example from Jan 31 to Feb 1, DateDif (excel) will calculate no months since it is NOT a full month, but DateDiff (VBA) will calculate 1 month since the month has changed.]

    Steve

  15. #15
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    You guys are stepping into another world with the macros. I appreciate it but I don't speak the language. But thank you all. SDCKAPR last reply looks like the winner that I need. Thank you SDCKAPR

    Thank you all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •