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
Joined
Feb 24, 2014
Messages
2,721
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

please help if you can i'm stuck

thanks

Tony
 

Some videos you may like

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
Joined
Jul 14, 2008
Messages
3,097
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top