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

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
Asked:
donnie91910
  • 4
  • 3
2 Solutions
 
Subodh Tiwari (Neeraj)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"))

Open in new window

0
 
Rob HensonFinance 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","")))

Open in new window


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

Open in new window

0
 
Rob HensonFinance AnalystCommented:
My suggestion also gives desired result.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Variance.jpg
0
 
Rob HensonFinance 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
 
Rob HensonFinance 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
 
Subodh Tiwari (Neeraj)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")))

Open in new window


TimeVariance.jpg
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now