Ask Your Question

Calc - Total Number of Days + Hrs in Date Range [closed]

asked 2013-12-03 14:40:08 +0200

spacemonkey gravatar image

Hi I am attempting to make a spreadsheet to calculate the total number of days + hours passed between a date range. To do this, I have a multiple colomns.

  1. Opening Date
  2. Closing Date
  3. Opening Hour
  4. Closing Hour

Is there a way for me to calculate Total Days and Total Hours based on the aforementioned columns. When I tried the =datediff with the formula =datediff(o2,p2,d) i get a #NAME? error, where O2 is the cell for Opening Date and P2 is the cell for Closing Date. No idea how to calculate the total number of hours if the number of hours exceeds over 24 hours, which it does in this case.

Help :)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-16 00:06:44.152387


Sorry, I just realized I overthought the Date function unnecessarily. I managed to get the total number of days by just subtracting P2 from O2 with =P2-o2 as the formula and then formatting the cell as a number. But I am unable to do the time.

spacemonkey gravatar imagespacemonkey ( 2013-12-03 15:22:32 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2013-12-03 15:22:05 +0200

erAck gravatar image

For each data row add a column with a formula calculating the datetime difference, for example if your 1.-4. are in columns O-R then in S2: =Q2+R2-(O2+P2) and copy that formula to all rows you want to calculate. Set the number format of those cells in column S to [HH]:MM:SS or whatever precision you like to be displayed. The important part is the [HH] instead of HH as that does not wrap the hour value to 24 hours clock time. Then sum all rows, for example =SUM(S2:S99) if the last data row was 99. Again set the number format of that sum cell to for example [HH]:MM:SS.

edit flag offensive delete link more


Hi there First off, thank you for taking the time to reply. I dont think my question was understood correctly. I figured out how to calculate the total number of days by just subtracting the End Date from the Start Date. But unfortunately, I am unable to calculate the total number of hours that passed during that transaction, especially when the transaction has lasted longer than one day.

For example, if Open Date = 03/12/13 Open Hour = 03:00 AM Close Date = 05/12/13 Closing Hour = 05:00 PM

Then the Total Days using the formula will generate total days. I managed to calculate the total hours with a little help from your formatting with the formula =U2+(R2-Q2)

Thanks again :)

spacemonkey gravatar imagespacemonkey ( 2013-12-03 18:38:54 +0200 )edit

As you didn't mention what you have in column U I have no idea what you are talking about. My above formula =Q2+R2-(O2+P2) calculates the difference in days, where 24h==1d, and formats the result as hours:minutes:second (which actually for full hours is just multiplying the result by 24). So if you want to obtain the number of days of that result you just have to do =INT(S2); to obtain the number of remaining hours, the fraction leftover after the result of days, then would be =(S2-INT(S2))*24

erAck gravatar imageerAck ( 2013-12-03 19:32:52 +0200 )edit

Thanks man

spacemonkey gravatar imagespacemonkey ( 2013-12-04 18:34:36 +0200 )edit

Question Tools

1 follower


Asked: 2013-12-03 14:40:08 +0200

Seen: 1,782 times

Last updated: Dec 03 '13