# Thread: If commands with dates

1. ## 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. 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. 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

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

11. Nicely done RG!

12. 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. Steve,

Fantastic!

14. 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. 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
•