donnie91910
asked on
Excel formula that gives date variance in hours, days or months.
I need an Excel formula that will take two Date/Time fields in an Excel spreadsheet and give me the variance in Hours if the variance is in Hours, variance in Days if the variance is days and variance in Months if the variance is in months.
Variance-Example.xlsx
Variance-Example.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Rob Henson
My suggestion also gives desired result.
This is the output returned by the two solutions. I am not sure what the other expert tried to prove here.
Not trying to prove anything, just showing that there are different ways of doing this.
Differences with my solution versus original request:
1) I have allowed for partial hours by showing result to 1 decimal place
2) 2 Options on how to present plurals; result adjusting when plural or just showing (s) whether plural or not.
I would consider both of these improvements on the original requirement.
Differences with my solution versus original request:
1) I have allowed for partial hours by showing result to 1 decimal place
2) 2 Options on how to present plurals; result adjusting when plural or just showing (s) whether plural or not.
I would consider both of these improvements on the original requirement.
Brackets around the 's or even option for it to be omitted when not required. How can that be seen as not matching the desired output?
@Donnie
If by any chance, you need to show the time intervals in singulars and plurals both, you may try this...
If by any chance, you need to show the time intervals in singulars and plurals both, you may try this...
=IF(MONTH(B2)>MONTH(A2),(DATEDIF(A2,B2,"m")&IF(DATEDIF(A2,B2,"m")=1," Month"," Months")),IF(DAY(A2)=DAY(B2),(TEXT(B2-A2,"h")&IF(TEXT(B2-A2,"h")+0=1," Hour"," Hours")),DATEDIF(A2,B2,"d")&IF(DATEDIF(A2,B2,"d")=1," Day"," Days")))