How to Convert the time into the decimal point for the total hours?

Forums › Microsoft Windows › How to Convert the time into the decimal point for the total hours?

jackyleunght2002

406 posts

Ultimate Geek
+1 received by user: 26

#296412 15-Jun-2022 13:22

Hello there.

Just want to know, i am on excel at the moment.

let say a worker is working Start on Column A1 09.00- Finishing Time B1 18.00 and minus the break C1 0.30 is 8.5 hours

so on the D1, how should I program the excel sheet to show 8.5 hours on the excel sheet.

thank you very much

Jacky

mdf

3566 posts

Uber Geek
+1 received by user: 1519

Trusted

#2927936 15-Jun-2022 13:37

This is how I do it. Subtract the start time from the end time in D1:

=B1-A1

Set that column to an hour:minute display with a custom number format (hh:mm).

Calculate a subtotal in minutes in E1:

=HOUR(D1)*60+MINUTE(D1)

Optional: You could also subtract your break time C1 (assuming in minutes) at this stage:

=HOUR(D1)*60+MINUTE(D1)-C1

Convert to decimal hours:

=E1/60

Round as appropriate using number formats.

Protip: Control+Shift+; will insert the current time into the selected cell automatically.

Behodar

11089 posts

Uber Geek
+1 received by user: 6069

Trusted

Lifetime subscriber

#2927949 15-Jun-2022 14:16

That seems a little complex. In D1, just put =(B1-A1-C1)*24 and format as a number. That seems to give the required result (8.5).

The *24 is because formatting a time as a number will usually report it in decimal days. Since we want decimal hours, we multiply by 24. Hopefully nobody works across the daylight saving changeover :)

jackyleunght2002

406 posts

Ultimate Geek
+1 received by user: 26

#2927998 15-Jun-2022 15:39

Behodar:

That seems a little complex. In D1, just put =(B1-A1-C1)*24 and format as a number. That seems to give the required result (8.5).

The *24 is because formatting a time as a number will usually report it in decimal days. Since we want decimal hours, we multiply by 24. Hopefully nobody works across the daylight saving changeover :)

Thank you very much Behodar!

i got this working now, much appreciated

Jacky