Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
I am looking for a formula that will add the hours worked each day for
seven days, adding the hours in a cell at the end of the week as they are entered each day. For example: a person works 8 am - 5 pm, Mon - Fri. and has 1 hour lunch each day. What formula would calculate the time into hours and add the total hours worked minus the lunch time on a daily basis. Any help would be appreciated! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
golfn --
The trick is to remember that, no matter what you see, Excel thinks of dates and times in a particular way --- XXXXX.YYYYYY where XXXXX is the number of days after 1/1/1900, and YYYYYY is the amount of the day after 12:00:00 midnight. So 0.5 is 1/2 day, or 12 noon. So here goes: A B C 1 8 am 5 pm =(b1-a1)*24-1 You multiply times 24 to convert the % of the day elapsed into the number of hours, then subtract 1 hour for lunch. You can, of course, build in rounding to the nearest 15 minutes, accounting for people working half-days not taking lunches, etc. But it's all adding to the basic idea. HTH " wrote: I am looking for a formula that will add the hours worked each day for seven days, adding the hours in a cell at the end of the week as they are entered each day. For example: a person works 8 am - 5 pm, Mon - Fri. and has 1 hour lunch each day. What formula would calculate the time into hours and add the total hours worked minus the lunch time on a daily basis. Any help would be appreciated! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula
Hi
With start time in A1 and End time in B1, enter in C1 =(B1-A1)*24-1 Copy down through C2:C7 In C8 =SUM(C1:C7) Times in Excel are stored as fractions of a day, hence the need to multiply by 24. If the times are likely to cross midnight, then use =MOD(B1-A1,1)*24-1 -- Regards Roger Govier wrote in message ... I am looking for a formula that will add the hours worked each day for seven days, adding the hours in a cell at the end of the week as they are entered each day. For example: a person works 8 am - 5 pm, Mon - Fri. and has 1 hour lunch each day. What formula would calculate the time into hours and add the total hours worked minus the lunch time on a daily basis. Any help would be appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
How do I view formula results intead of formula in excel? | Excel Worksheet Functions | |||
i edit a formula (excel) then it displays formula not answer | Excel Discussion (Misc queries) |