# Timesheet - Time data issue



## Jods70 (Dec 20, 2022)

Hi guys. 

First post but I've spent the last 6 hours trying to figure this one out. I work for a transport company & our drivers currently fill in their timesheets on printed paper, then take a photo of it & email it to me so I can process their pay. To say this is time-consuming is an understatement as I need to enter their times manually to then work out the number of hours over 80 hours as this needs to be entered seperately.

I'm trying to make life easier for them & myself by having the spreadsheet calculate all of this as the data is entered, my problem is they will be entering the data on either a tablet or mobile phone & having to enter the : "colon" for excel to register that they are times won't really work with my guys.

I'm currently using custom cell formatting of hhmm but it's not always working as the cells change to date/time format & throughs all the calculations out per my image.

Any & all help will be most appreciated. Thanks Jods


----------



## Dave Patton (Dec 20, 2022)

Adjust the columns as necessary.
You can paste this post into a clean sheet and review the formulas.  Select the cell below the f(x) in the heading and then move to your sheet and paste.
Probably K5 is the relevant formula.  K3 and K4 are just initial examples of the concept.

Conversion.xlsmABCDEFGHIJKLMNO2DayDateStartLunchLunchDinnerDinnerFinishBase3MonDec-12061509301200200011.2588.0026,993.2527,001.2540800093010001200130018309.0088.0050800093010001200130018309.0088.002eCell FormulasRangeFormulaO3O3=N3+M3K3K3=(TEXT(D3,"00\:00")-TEXT(C3,"00\:00"))*24+(TEXT(H3,"00\:00")-TEXT(G3,"00\:00"))*24K4K4=(TEXT(D4,"00\:00")-TEXT(C4,"00\:00"))*24+(TEXT(F4,"00\:00")-TEXT(E4,"00\:00"))*24+(TEXT(H4,"00\:00")-TEXT(G4,"00\:00"))*24K5K5=(TEXT(D5,"00\:00")-TEXT(C5,"00\:00")+TEXT(F5,"00\:00")-TEXT(E5,"00\:00")+TEXT(H5,"00\:00")-TEXT(G5,"00\:00"))*24M3:M5M3=MIN(L3,K3)


----------



## Jods70 (Dec 21, 2022)

Dave Patton said:


> Adjust the columns as necessary.
> You can paste this post into a clean sheet and review the formulas.  Select the cell below the f(x) in the heading and then move to your sheet and paste.
> Probably K5 is the relevant formula.  K3 and K4 are just initial examples of the concept.
> 
> Conversion.xlsmABCDEFGHIJKLMNO2DayDateStartLunchLunchDinnerDinnerFinishBase3MonDec-12061509301200200011.2588.0026,993.2527,001.2540800093010001200130018309.0088.0050800093010001200130018309.0088.002eCell FormulasRangeFormulaO3O3=N3+M3K3K3=(TEXT(D3,"00\:00")-TEXT(C3,"00\:00"))*24+(TEXT(H3,"00\:00")-TEXT(G3,"00\:00"))*24K4K4=(TEXT(D4,"00\:00")-TEXT(C4,"00\:00"))*24+(TEXT(F4,"00\:00")-TEXT(E4,"00\:00"))*24+(TEXT(H4,"00\:00")-TEXT(G4,"00\:00"))*24K5K5=(TEXT(D5,"00\:00")-TEXT(C5,"00\:00")+TEXT(F5,"00\:00")-TEXT(E5,"00\:00")+TEXT(H5,"00\:00")-TEXT(G5,"00\:00"))*24M3:M5M3=MIN(L3,K3)


Hi Dave.

Thank you for this but it isn't working for me  I've also been informed today that the drivers require a third break period. Here's the first few rows of what I'm trying to fix, that might make it easier to figure out what I've done wrong. Thanks again for the assistance 

Fortnightly Time Sheets - MTAHRFORM3.0.xlsmABCDEFGHIJKLMNOPQRSTU3DayDateStartLunchLunch DinnerDinner SupperSupperFinishBase 8Over 8TotalJob Description: Ref #'s Con-Notes4TimeStartFinishStartFinishStartFinishTimeHrs/DayHrs/DayHrs5Mon12-Dec8009000000239223936Tue13-Dec0007Wed14-Dec0008Thu15-Dec000TimesheetCell FormulasRangeFormulaK5K5=(TEXT(D5,"00\:00")-TEXT(C5,"00\:00")+TEXT(F5,"00\:00")-TEXT(E5,"00\:00")+TEXT(H5,"00\:00")-TEXT(G5,"00\:00"))*24L5:L8L5=IF(((D5-C5)+(F5-E5)+(H5-G5))*24>8,((D5-C5)+(F5-E5)+(H5-G5))*24-8,0)M5:M8M5=L5+K5K6:K8K6=(MOD(G6-B6,1)-MOD(D6-C6,1)-MOD(F6-E6,1))*24B5B5=N2-13B6B6=N2-12B7B7=N2-11B8B8=N2-10


----------



## Dave Patton (Dec 21, 2022)

Conversion.xlsmABCDEFGHIJKLMN1info23DateStartLunchLunch DinnerDinner SupperSupperFinishTotalBase 8Over 8Total4TimeStartFinishStartFinishStartFinishTimeHoursHrs/DayHrs/DayHrs5Mon 12-Dec-2280090011016Tue 13-Dec-228009009301250132018001830200098192eeCell FormulasRangeFormulaK5:K6K5=(TEXT(D5,"00\:00")-TEXT(C5,"00\:00")+TEXT(F5,"00\:00")-TEXT(E5,"00\:00")+TEXT(H5,"00\:00")-TEXT(G5,"00\:00"))*24L5:L6L5=MIN(K5,8)M5:M6M5=IF(K5>8,K5-8,0)N5:N6N5=L5+M5


----------



## Jods70 (Dec 22, 2022)

Dave Patton said:


> Conversion.xlsmABCDEFGHIJKLMN1info23DateStartLunchLunch DinnerDinner SupperSupperFinishTotalBase 8Over 8Total4TimeStartFinishStartFinishStartFinishTimeHoursHrs/DayHrs/DayHrs5Mon 12-Dec-2280090011016Tue 13-Dec-228009009301250132018001830200098192eeCell FormulasRangeFormulaK5:K6K5=(TEXT(D5,"00\:00")-TEXT(C5,"00\:00")+TEXT(F5,"00\:00")-TEXT(E5,"00\:00")+TEXT(H5,"00\:00")-TEXT(G5,"00\:00"))*24L5:L6L5=MIN(K5,8)M5:M6M5=IF(K5>8,K5-8,0)N5:N6N5=L5+M5


Thank you SO MUCH Dave that worked a treat. Have a wonderful Xmas & New Year


----------



## Dave Patton (Dec 22, 2022)

Thanks for the feedback and Merry Christmas to you.


----------

