two days time period (Office 2003)

Topic

New Reply

On Monday at (b12)19:54:57 we started a job that ran until the next day at (d12)12:20 pm. How can I figure out the total number of hours? I tried D12 – B12.

Reply | Quote

Replies

The best solution is to put the date+time in the cells. You can then use =D12-B12. This will be a date/time value. If you really need the number of hours, multiply by 24: =24*(D12-B12)

Alternatively, you can put the dates and times in separate cells, say date1 in A12, time1 in B12, date2 in C12, time2 in D12. You can then use =(C12+D12)-(A12+B12). Again, this will be a date/time value. To get the number of hours, use =24*((C12+D12)-(A12+B12)).

Reply | Quote

Hans,
Can the square brackets not be used here too?
If you minus the two time values and format the answer to [hh], it will give the total in hours as a value, not a time. Specifying [m] in square brackets will give the total number of minutes.

Reply | Quote

Depends on what Daniel wants. If he just wants to see the time difference in hours, the [h] or [hh] format will do fine. If he needs to do further calculations with the number of hours, he will need to multiply by 24 somewhere along the line.

Reply | Quote

… and the key to displaying hours in time format is to format the cell properly.

Either do the maths as Hans suggests and get decimal hours by dividing by 24 into a number field

Or use custom format and set it to [h]:mm:ss where th square brackets are required – then dividing by 24 isn’t needed.

Reply | Quote

We tried doing the format cells but that wouldn’t work. We did the formula and got it going.

Thanks

Reply | Quote