Trying to make a time tool

bh24524

Active Member
Joined
Dec 11, 2008
Messages
317
Office Version
  1. 2021
  2. 2007
Can anyone tell me how I might do the below multiple part scenario:

I need to subtract an end time and a start time to get the hours and the minutes. Start time is in P2 and end time is in Q2. I wanted to do this in a way that I actually don't need to enter the colon and could just enter the time as a straight number and it automatically put the colon in there. To do that, I formatted the cells with the start and end times with a custom format of "00\:00"

here's where it gets a bit tricky. I need another cell to show the difference between the two times in decimal format. That would be cell Q3. Right now I have Q3 showing the hours and minutes so for example, in P2. I have 11:12 and in Q2 I have 12:17. Q3 shows 1:05.

So for that 1 hour and 5 minutes, is there a way I can get that to show as a decimal ultimately reading 1.08? (yes 8 not 5 I had divided the 5 mins by 60)
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I wanted to do this in a way that I actually don't need to enter the colon and could just enter the time as a straight number and it automatically put the colon in there. To do that, I formatted the cells with the start and end times with a custom format of "00\:00"

Hi, in my opinion this is a bad idea - you end up jumping through hoops whenever you want to perform calculations on those times - that said, here is a formula you can try.


Excel 2013/2016
PQ
1StartEnd
211121217
31.083333
Sheet1
Cell Formulas
RangeFormula
Q3=(TEXT(Q2,"00\:00")-TEXT(P2,"00\:00"))*24
 
Upvote 0
Thank you for your help on that. That should give me what I need.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top