Zac123
asked on
Calculate days between two dates
Hi , i'm using this code which calulates the number of days between two dates however if the two dates are the same day i get = 0. for eample:
24/11/2016 to 24/11/2016 = 0
I need it to say:
24/11/2016 to 24/11/2016 = 1
or another example:
24/11/2016 to 25/11/2016 = 2
thanks you
zac
24/11/2016 to 24/11/2016 = 0
I need it to say:
24/11/2016 to 24/11/2016 = 1
or another example:
24/11/2016 to 25/11/2016 = 2
targetSheet.getRange(targetSheet.getLastRow(),numColumns + 1).setFormula("=DATEDIF(R[0]C[-8],R[0]C[-1], \"D\")");
thanks you
zac
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Miss_Sellaneus - Using MAX would get round the same day issue, making the 0 become 1 but it wouldn't get round the other example, that would still say 1 rather than 2.
Yes, I know.
In my hurry to answer I missed that the author wanted that for all of it.
In my hurry to answer I missed that the author wanted that for all of it.
ASKER
thank you very much. my code now looks like this and works:
targetSheet.getRange(targe tSheet.get LastRow(), numColumns + 1).setFormula("=DATEDIF(R[ 0]C[-8],R[ 0]C[-1],\" D\")+1");
targetSheet.getRange(targe
To expand a bit on how it works.....
Excel stores dates as an integer serial number based on the number of days since the start of time; the start of time being 01 Jan 1900 in Excel's brain. Therefore, to calculate the number of days between two dates you have to deduct the earlier date from the later date.
Today (24 Nov 16) is day 42698, yesterday was therefore 42697. To calculate number of days you would have a formula that says "=Today minus Yesterday" which excel reads as "=42698-42697" and the result is obviously 1.
When working with dates it is quite common to have to adjust the result by 1. Some examples:
1) If looking at a working week you would say that Monday to Friday is 5 days yet if you had Friday Date minus Monday Date the result would be 4; need to add 1.
2) If an event was being held over a weekend, we would say it was 2 days; but Sunday Date minus Saturday Date = 1, need to add 1.
Excel gets even more complicated when time is also involved but that's another topic!!!
Excel stores dates as an integer serial number based on the number of days since the start of time; the start of time being 01 Jan 1900 in Excel's brain. Therefore, to calculate the number of days between two dates you have to deduct the earlier date from the later date.
Today (24 Nov 16) is day 42698, yesterday was therefore 42697. To calculate number of days you would have a formula that says "=Today minus Yesterday" which excel reads as "=42698-42697" and the result is obviously 1.
When working with dates it is quite common to have to adjust the result by 1. Some examples:
1) If looking at a working week you would say that Monday to Friday is 5 days yet if you had Friday Date minus Monday Date the result would be 4; need to add 1.
2) If an event was being held over a weekend, we would say it was 2 days; but Sunday Date minus Saturday Date = 1, need to add 1.
Excel gets even more complicated when time is also involved but that's another topic!!!
ASKER
thanks. makes so much more sense when written in plain English.
=MAX(1,DATEDIF(R[0]C[-8],R