# macro to work out number of hours and minutes worked and convert into decimal, and if possible how many were before a set time?

#### tonywatsonhelp

##### Well-known Member
Hi Everyone,
Just sorting out next years time sheet and i have a few problems,

I know time can be entered into excel as a time like "12:30:00"
but this is both difficult and slow so i want to be able to enter a start time and and end time as you do numbers so 10.00 to 14.45 for example
so this is what i need

Start time is in "D10"
End Time is in "E10" work out the hours worked as a decimal. so 3 and half hours is "3.50" etc
the another formula id possible to tell me how much time was before 7am?

so if you worked 5.30 till 17.00 thats 11 and half hours so first formula gives me 11.50 second formula gives me 1.50 for the hours before 7am

thanks

Tony

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### lrobbo314

##### Well-known Member
Like this...

Book1
DEFGHI
9StartEndTotalHrs Before 7Total TimeTime Minus Before 7
105.5017.0011.501.5011 hrs 30 min10 hrs 00 min
Sheet2
Cell Formulas
RangeFormula
F10F10=E10-D10
G10G10=7-D10
H10H10=(F10*((1/24/60)*60))
I10I10=(F10-G10)*((1/24/60)*60)

Cell G10 has a custom format

Code:
``hh \h\r\s mm \mi\n``

Replies
3
Views
479
Replies
6
Views
400
Replies
4
Views
619
Replies
2
Views
84
Replies
4
Views
327

1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

### 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.

### Which adblocker are you using?

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

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