| Author |
Message |
huddy33
Groupie
Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
|
   Topic: DateDiff returning wrong value
Posted: 15 May 2012 at 10:17am |
|
I have the following formula for calculating the difference between two dates. I need to run a calculation once I get the correct value.
DateDiff("d",{Meeting.TravelStartDate},{Meeting.TravelEndDate})
The date set for this particular report is 4/29/12 to 5/4/12. It spans two months and a weekend. The formula above is returning 5 when it needs to return 6. I've tried everything, please help!
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 May 2012 at 10:59am |
if you always want to calculate this as 1 more day than the standard function then add 1 to the value...
DateDiff("d",{Meeting.TravelStartDate},{Meeting.TravelEndDate})+1
Edited by DBlank - 15 May 2012 at 10:59am
|
|
IP Logged |
|
huddy33
Groupie
Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
|
Posted: 15 May 2012 at 11:09am |
|
I have tried that and while it works for the above date set, it's wrong for any set that doesn't span a weekend/month switch.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 May 2012 at 11:14am |
|
can you please explain with more exact specificity what you want calculated with your extra conditions?
Edited by DBlank - 15 May 2012 at 11:15am
|
|
IP Logged |
|
huddy33
Groupie
Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
|
Posted: 15 May 2012 at 11:35am |
|
I need the exact number of days between those two days displayed as an integer. The reg datediff works except with situations like the date set above. It throws it off somehow. I'm thinking it's bc of the month switch.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 May 2012 at 11:47am |
I am confused
DateDiff("d",date(2012,5,1),date(2012,5,6)) returns 5 the same as
DateDiff("d",date(2012,4,29),date(2012,5,4)).
What is the difference here that you think should be happening? If you look at a calendar and count the days the same way you will get 5 in both instances...?
Edited by DBlank - 15 May 2012 at 11:48am
|
|
IP Logged |
|
huddy33
Groupie
Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
|
Posted: 15 May 2012 at 12:07pm |
|
I need it to show as six days bc that is the actual day count. 29, 30, 1, 2, 3, and 4.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 May 2012 at 2:30pm |
|
That would also make the other one six. First, second, third, fourth, fifth, and sixth.
Sorry, I just cannot see the difference in the two examples ranges I gave you. If you want it include the first day in the difference I understand, but I don't get why you do it in one instance and not the other...
Edited by DBlank - 15 May 2012 at 2:32pm
|
|
IP Logged |
|
huddy33
Groupie
Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
|
Posted: 15 May 2012 at 3:16pm |
|
I want it to catch each individual day every time. When I add the +1 to the date diff, it messes up every non-month switch. I just need it to be accurate.
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 15 May 2012 at 10:57pm |
No it doesn't, DBlank is right.
Give us examples of other date ranges where it messes up on non-month switches.
29/4 - 4/5 - 30, 1, 2, 3 , 4 - 5
10/4 - 17/4 - 11, 12, 13, 14, 15, 16, 17 - 7
Your math is confused as you think becuase 17-10 = 7 it is giving you the correct number. Your datediff is working correctly no matter how you look at it! If you want your report to be accurate and include the first date as a day ie 6 day between the first date range, 8 is the correct answer for the second date range.
|
|
IP Logged |
|
|
|
