Timesheets with Unpaid Half Hour

[grantrobbins22]

Hello All,

I need a timesheet that deducts half an hour when the person works after 1pm, basically lunch is 12.30pm to 1pm and if they take it or not 30 mins are deducted. Therefore the formula needs to deduct half an hour if the time if after 1pm.

For information i am using military hours and i cannot use hours as they can start at any time in the morning etc.

Any help would be appreciated.

Regards
Grant

 

[bewsh1987]

the below will give you 30 mins

value("00:30:00")

 

[grantrobbins22]

How can I deduct that though if it the time is after 1pm?

 

[bewsh1987]

something like



=IF(L15>VALUE("13:00:00"),L15-VALUE("00:30:00"),L15)

 

[grantrobbins22]

How do you then total up the hours in the week as it becomes a decimal, and what format would it need to be in

 

[bewsh1987]

you can use =text(Value, Format_text)

=TEXT(IF(A1>VALUE("13:00:00"),A1-VALUE("00:30:00"),A1),"HH:MM:SS")