Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet - Formula
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet - Formula
5 hours is 5/24, or TIME(5,,)
-- David Biddulph --Viewpoint wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet - Formula
"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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet - Formula
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. "JLatham" wrote: "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" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet - Formula
Great - thanks for your help.
"David Biddulph" wrote: 5 hours is 5/24, or TIME(5,,) -- David Biddulph --Viewpoint wrote: 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Timesheet formula problem | Excel Worksheet Functions | |||
timesheet and overtime formula/s | Excel Discussion (Misc queries) | |||
Need help with a timesheet formula | Excel Discussion (Misc queries) | |||
Timesheet Formula Help Please! | Excel Worksheet Functions | |||
TIMESHEET FORMULA | Excel Worksheet Functions |