Calc - Total Number of Days + Hrs in Date Range

[spacemonkey]

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

[spacemonkey]

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.

[erAck]

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.

[spacemonkey]

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

[erAck]

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

[spacemonkey]

Thanks man