Results 1 to 15 of 15
Thread: If commands with dates

20140121, 17:46 #1
 Join Date
 Jun 2005
 Posts
 406
 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.

20140121, 18:12 #2
 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

20140122, 00:06 #3
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 3,038
 Thanks
 166
 Thanked 800 Times in 729 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,
MaudLast edited by Maudibe; 20140122 at 08:18.

20140209, 21:56 #4
 Join Date
 Jun 2005
 Posts
 406
 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

20140210, 04:10 #5
 Join Date
 Dec 2009
 Location
 Earth
 Posts
 9,112
 Thanks
 64
 Thanked 1,125 Times in 1,048 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

20140210, 06:21 #6
 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

20140210, 06:27 #7
 Join Date
 Dec 2009
 Location
 Earth
 Posts
 9,112
 Thanks
 64
 Thanked 1,125 Times in 1,048 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

20140210, 07:32 #8
 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"

20140210, 07:44 #9
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 3,038
 Thanks
 166
 Thanked 800 Times in 729 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

20140210, 09:25 #10
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 10,013
 Thanks
 423
 Thanked 1,608 Times in 1,452 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
HTHLast edited by RetiredGeek; 20140210 at 13:47.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20140210, 13:27 #11
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 3,038
 Thanks
 166
 Thanked 800 Times in 729 Posts
Nicely done RG!

20140210, 14:00 #12
 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
Steve

20140210, 14:02 #13
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 10,013
 Thanks
 423
 Thanked 1,608 Times in 1,452 Posts
Steve,
Fantastic!May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20140210, 14:21 #14
 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

20140211, 21:34 #15
 Join Date
 Jun 2005
 Posts
 406
 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.