MS Excel ...total hrs worked formula

[Lloydm]

Not that of a guru with excel but tryin to write a formula that will calculate total hrs worked for normal mon-fri 9a-5p week schedule. I have figured out how to calculate daily totals but for week total I would assume I need an "if" statement. I am not sure how to write this but would greatly appreciate some help.
thank you

Day    Time In    Time Out    Time In    Time Out    Total Hrs
mon    9:00    13:00    14:00    17:00    8:00
tue    9:00    13:00    14:00    17:00    8:00
wed    9:00    13:00    14:00    17:00    8:00
thu    9:00    13:00    14:00    17:00    8:00
fri    9:00    13:00    14:00    17:00    8:00
            Week Total              [need formula or code]

[ronverdonk]

Why don't you use the standard Excel SUM() function, whereby you sum the previous 5 cells?

Ronald

[Ganon11]

Not that of a guru with excel but tryin to write a formula that will calculate total hrs worked for normal mon-fri 9a-5p week schedule. I have figured out how to calculate daily totals but for week total I would assume I need an "if" statement. I am not sure how to write this but would greatly appreciate some help.
thank you

I don't think you'll need any sort of if statement. Looking at your data,

Day    Time In     Time Out   Time In    Time Out    Total Hrs
mon    9:00     13:00       14:00        17:00        8:00
tue    9:00     13:00       14:00        17:00        8:00
wed    9:00     13:00       14:00    17:00        8:00
thu    9:00     13:00       14:00    17:00        8:00
fri    9:00     13:00       14:00        17:00        8:00
            Week Total              

I assume the Total Hrs cells are formulas totaling the hours for that day. Then your Week Total cell can have a formula adding up the Total Hrs values as you did with the daily totals.

[bartonc]

Not that of a guru with excel but tryin to write a formula that will calculate total hrs worked for normal mon-fri 9a-5p week schedule. I have figured out how to calculate daily totals but for week total I would assume I need an "if" statement. I am not sure how to write this but would greatly appreciate some help.
thank you

Day    Time In    Time Out    Time In    Time Out    Total Hrs
mon    9:00    13:00    14:00    17:00    8:00
tue    9:00    13:00    14:00    17:00    8:00
wed    9:00    13:00    14:00    17:00    8:00
thu    9:00    13:00    14:00    17:00    8:00
fri    9:00    13:00    14:00    17:00    8:00
            Week Total              [need formula or code]

In my timesheet, column a is an actual date. This forumul allows for more that one project to be worked on for a certain date.

=IF(A13=A14;"";G13)
=IF(H13="";"";(H13*17)+K13)

My columns look something like this

Day    Time In    Time Out    Break    Project    Total Hrs
1/1/07    9:00    11:30            2.5
1/1/07    11:30    17:00    1        4.5
1/2/07    9:00    17:00    1        7
 
            Week Total              [need formula or code]
=SUM(I13:I158)+E7
 

I guess no one noticed that 9 to 5 minus one doesn't equal 8 hours.

[bartonc]

Sorry, time (5 min.) to edit ran out.

=IF(NOT(E13="");((E13-D13)*24)-F13;"")
=IF(A13=A14;"";G13)
=IF(H13="";"";(H13*17)+K13)

My columns look something like this

Day    Project    Time In    Time Out    Break    Hours    Day hours    Total $
1/1/07        9:00    11:30        2.5
1/1/07        11:30    17:00    1    4.5    7
1/2/07        9:00    17:00    1    7    7
 
            Week Total              [need formula or code]
=SUM(I13:I158)+E7
 

I guess no one noticed that 9 to 5 minus one doesn't equal 8 hours.

[Lloydm]

Why don't you use the standard Excel SUM() function, whereby you sum the previous 5 cells?

Ronald

I tried doing it but no luck. Seems as if whenever the total hrs = 24:00 it resets to 0:00 resulting in 16:00 total work week hrs [ my formula -> =SUM(F4:F8) ]

Total Hrs
8:00
8:00
8:00
8:00
8:00
----------
16:00

[Lloydm]

Sorry, time (5 min.) to edit ran out.

=IF(NOT(E13="");((E13-D13)*24)-F13;"")
=IF(A13=A14;"";G13)
=IF(H13="";"";(H13*17)+K13)

My columns look something like this

Day    Project    Time In    Time Out    Break    Hours    Day hours    Total $
1/1/07        9:00    11:30        2.5
1/1/07        11:30    17:00    1    4.5    7
1/2/07        9:00    17:00    1    7    7
 
            Week Total              [need formula or code]
=SUM(I13:I158)+E7
 

I guess no one noticed that 9 to 5 minus one doesn't equal 8 hours.

Thanks for your reply, I included no calculation -1 for lunch time I simply calculated total hrs for workday as end time - start time

Day---------Time In--------Time Out----Time In----Time Out------Total Hrs
mon--------- 9:00-----------13:00-----------14:00------17:00-----------8:00

seems as if when total work week hrs = 24:00 it resets itself to 0:00 resulting in 16:00 for work week

[bartonc]

Thanks for your reply, I included no calculation -1 for lunch time I simply calculated total hrs for workday as end time - start time

Day---------Time In--------Time Out----Time In----Time Out------Total Hrs
mon--------- 9:00-----------13:00-----------14:00------17:00-----------8:00

seems as if when total work week hrs = 24:00 it resets itself to 0:00 resulting in 16:00 for work week

You're welcome. I hope you can make use of those formulas. They solve this by converting to a decimal before the SUM() is used.

[Lloydm]

Here is what I've summarized.

1hr = 1/24 = 0.4166667 or rounded 0.417
1min = 1/24 x 60 = 0.000694
1sec = 1/24 x 60 x 60 = 0.00001157


Thus total hrs for any workday =8/24 as decimal = 0.333333333 rounded to 0.33

Total hrs for mon-fri would be 0.333333333 x 5 = 1.666666667
So we conclude total hrs (40) for mon-fri = 1.666666667 as serial or ie decimal
To convert a serial number to an hour I used the excel formula =hour(decimal value goes here) or ie hour(1.666666667)

My sheet is prefomatted with millitary time for allocated cells
The thing is, using =hour(decimal value) for each workday then =SUM(mon:fri) I have a valid answer of 40.
Using the formatted section will show as 0:00 when calculated, however when using sum funcition I arrive @ 960:00:00. I then divided 960:00:00/24hrs = 40:00:00

DAY-----Time-----Serial-------- =hour(serial)--------=hour(serial) with millitaryformat
mon-----8:00-----0.333333333--------8------------------------ 0:00
tue-------8:00-----0.333333333--------8------------------------ 0:00
wed------8:00-----0.333333333--------8------------------------ 0:00
thu-------8:00-----0.333333333--------8------------------------ 0:00
fri---------8:00-----0.333333333--------8------------------------ 0:00
TOTAL--16:00----1.666666667------40------------------ 960:00:00

TOTAL VALUE IN MILLITARY TIME 40:00:00 (960:00:00/24hrs)



I changed various day hrs and the formula seems to adjust to correct totalHrsWeek value when changed. It's always nice to find a resolution however If someone can also explain this more clearly to me I would appreciate

[bartonc]

What's wrong with

(E13-D13)*24

?

[Lloydm]

What's wrong with

(E13-D13)*24

?

I did but had no luck, I also found the following in excel help...

Formula Description (Result)
=SUM(A2:A5) Total hours worked when the total is less than a day (17:20)

=SUM(A2:A6)*24 Total hours worked when the total is greater than a day (25.33333).

I got my total hrs with equation.... =SUM(sun:mon)*24/24
thanks for your help. I DEEPLY & GREATLY APPRECIATE THIS
===============================================


Iv'e searched through the vb forum but had no luck finding a "print job" script. After saving file and before exit, I would like a script to prompt the user to send to printer

[bartonc]

I did but had no luck, I also found the following in excel help...

Formula Description (Result)
=SUM(A2:A5) Total hours worked when the total is less than a day (17:20)

=SUM(A2:A6)*24 Total hours worked when the total is greater than a day (25.33333).

I got my total hrs with equation.... =SUM(sun:mon)*24/24
thanks for your help. I DEEPLY & GREATLY APPRECIATE THIS
===============================================

You are welcome. I'm glad that you got it working.

Iv'e searched through the vb forum but had no luck finding a "print job" script. After saving file and before exit, I would like a script to prompt the user to send to printer

The VB forum is probably a good place to post this question.