I'm trying to use the data in a pivot table and the information is entered into one cell as dd hh:mm:ss. How would I break this out if I wanted to manipulate the data, would it be with a pivot table or formula? I have two columns next to each other with dd hh:mm:ss format with different times and I want to calculate the time elapsed between them. Anything helps!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Date Time Comparison
- Thread starter yessir
- Start date
I want to calculate how much time change has occurred. The format is DATE HOUR:MINUTE:SECOND. I want to find the difference X. Hope this helps
| Time | Time | Difference |
| 2 4:30:01 | 2 5:01:01 | X |
| 4 23:55:00 | 5 00:25:00 | X |
| 5 22:12:00 | 6 01:00:00 | X |
| 1 12:00:01 | 1 17:00:00 | X |
Upvote
0
Cubist
Well-known Member
- Joined
- Oct 5, 2023
- Messages
- 2,139
- Office Version
- 365
- Platform
- Windows
- MacOS
Since they're already in date/time format, you should be able to subtract them. The difference will be in days. Multiply by 24 to get hours.
| Book1 | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Time | Time | Difference in hours | ||
| 2 | 2 04:30:01 | 2 05:01:01 | 0.517 | ||
| 3 | 4 23:55:00 | 5 00:25:00 | 0.500 | ||
| 4 | 5 22:12:00 | 6 01:00:00 | 2.800 | ||
| 5 | 1 12:00:00 | 1 17:00:00 | 5.000 | ||
Sheet1 | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C5 | C2 | =(B2-A2)*24 |
Upvote
0
