CODEWITHSUNDEEP
excel-formulaexcel-2010
swa
swa

Reputation: 91

Formula for Total hours between two date times in ms excel

I need to calculate the total hours and minutes between two dates mentioned in excel. i.e.,

Start date: 02-Sep-2013 02:03:00 AM
End Date: 04-Sep-2013 02:04:00 AM

The answer should be 48:01 h:mm.
Need to consider holidays also, like if 03-Sep-2013 is holiday, then The answer should be 24:01 h:mm.
There are no business hours. It should consider 24 hours. Please help me on this.

Upvotes: 6

Views: 94368

Answers (3)

Paul Wichtendahl
Paul Wichtendahl

Reputation: 127

The problem with many of the example answers given is definitional. If you are looking for elapsed time finer than days in significant digits the "B2-B1" answer given is incorrect if the end time is earlier in the day than the start time.

Depending on what I am trying to extract I use variations of the following formula to get a more precise "elapsed time". Depending on why a person lands on this answer the difference in result can make a difference.

    =ROUNDDOWN(B3-B2,0) & " Days " 
     & ROUNDDOWN(MOD(B3-B2,1)*24,0) & " hours " 
     & TEXT(MOD(MOD(B3-B2,1)*24,1)*60,"#0") & " Mins"

(formatted for easier reading)

The image below shows the results of the different answers proposed. I am not saying the other answers are wrong just that they may not be precise enough depending on the purpose of the answer seeker. enter image description here

Upvotes: 0

SeanC
SeanC

Reputation: 15923

a simple subtraction will get you the value you want: subtract one date/time from the other, and then subtract the number of holiday dates that fall between the two dates.

the trick is to format the result cell as [h]:mm (this format works for up to 9999 hours)

Here's an example:
enter image description here

Upvotes: 10

Vaibs_Cool
Vaibs_Cool

Reputation: 6156

Hours = ((End_Date+End_Time)-(Start_Date+Start_Time))*24

For more details please refer the below link

Excel

Another Formula with example

Cell A1: 2/15/2012  10:00:00 AM

Cell B1: 2/18/2012  08:00:00 PM

What is the elapsed time between both dates (in hours)?

You can get the answer by using the below formula in cell


    C1:= INT(B1-A1)*24+(((B1-A1)-INT(B1-A1))/0.04166666)

Upvotes: 5

Related Questions