• Status: Solved
• Priority: High
• Security: Public
• Views: 82

# 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
0
donnie91910
• 4
• 3
2 Solutions

Excel & VBA ExpertCommented:
You may try something like this...

In C2
``````=IF(AND(DAY(A2)=DAY(B2),MONTH(A2)=MONTH(B2)),TEXT(B2-A2,"h")&" hours",IF(MONTH(A2)=MONTH(B2),DATEDIF(A2,B2,"d")&" Days",DATEDIF(A2,B2,"m")&" Months"))
``````
0

Finance AnalystCommented:
Try one of these:

Allows for plural and adds "s"
``````=IF(B2-A2<1,TEXT((MOD(B2,1)-MOD(A2,1))*24,"0.0")&" Hour"&IF((MOD(B2,1)-MOD(A2,1))*24>1,"s",""),
IF(B2-A2<30,TEXT(DATEDIF(A2,B2,"d"),"0")&" Day"&IF(DATEDIF(A2,B2,"d")>1,"s",""),
TEXT(DATEDIF(A2,B2,"m"),"0")&" Month"&IF(DATEDIF(A2,B2,"m")>1,"s","")))
``````

Just puts (s) in case of plural:
``````=IF(B2-A2<1,TEXT((MOD(B2,1)-MOD(A2,1))*24,"0.0")&" Hour(s)",
IF(B2-A2<30,TEXT(DATEDIF(A2,B2,"d"),"0")&" Day(s)",
TEXT(DATEDIF(A2,B2,"m"),"0")&" Month(s)"))
``````
0

Finance AnalystCommented:
My suggestion also gives desired result.
0

Excel & VBA ExpertCommented:
This is the output returned by the two solutions. I am not sure what the other expert tried to prove here.

0

Finance AnalystCommented:
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.
0

Finance AnalystCommented:
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?
0

Excel & VBA ExpertCommented:
@Donnie

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")))
``````

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.