Excel Formula

[golfnjoe]

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!

 

[pdberger]

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

 

[Roger Govier]

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