Timesheet - Formula

[--Viewpoint]

Goal: I want to subtract lunch time if I work over 5 hours, otherwise I
want

to know the hours worked if under 5 hours)

Details:

Cell A10 - lunch time, cell is number format, time
Cell E10 - time I arrived at work, custom cell format h:mm AM/PM
Cell F10 - time I leave work, custom cell format h:mm AM/PM
Cell B10 - is cell to enter the formula and then convert it to decimal

=If(F10-E10 is greater than 5 hours, then F1-E10-A10, else, F10-E10)*24
(it is mandatory to take lunch if you work 5 hours or more)

Can anyone provide the correct formula and/or cell formats?

My problem is that I don't know how to recognize the 5 hours in the formula.

 

[David Biddulph]

5 hours is 5/24, or TIME(5,,)
--
David Biddulph

Details:

Cell A10 - lunch time, cell is number format, time
Cell E10 - time I arrived at work, custom cell format h:mm AM/PM
Cell F10 - time I leave work, custom cell format h:mm AM/PM
Cell B10 - is cell to enter the formula and then convert it to decimal

=If(F10-E10 is greater than 5 hours, then F1-E10-A10, else,
F10-E10)*24 (it is mandatory to take lunch if you work 5 hours or
more)

Can anyone provide the correct formula and/or cell formats?

My problem is that I don't know how to recognize the 5 hours in the
formula.

 

[JLatham]

"my problem is that I don't know how to recognize the 5 hours in the formula."

I'll give you that answer and I'll bet you can figure the rest out on your
own. 5 hours in 'Excel time' is .208333 (5 / 24). Excel tracks time in
terms of days and fractions of days. If you look at a date in General or
Number format, you'll find it's a large number: Feb 2, 2010 is actually
stored as 40211.0 in Excel (40,211 days after Jan 1, 1900). Hours, minutes
and seconds are stored as the decimal value to the right of the decimal
point, with .00 being Midnight at the start of the day.

So 5 hours = 5/24 of one day = 0.208333

Your formula could start off as something like:
=IF(F10-E10>.208333," greater than 5 hrs","less than or equal to 5 hrs")
or even
=IF(F10-E10>(5/24),"gt 5","not gt 5")

 

[--Viewpoint]

Great - thanks for your help.

5 hours is 5/24, or TIME(5,,)
--
David Biddulph





.

 

[--Viewpoint]

Thank you so much. It was so simple and I wished I had remembered this
concept as I was aware of it. I just don't have much experience with Excel
so I always need to reach out for help. Thanks, again.