By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,708 Members | 2,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,708 IT Pros & Developers. It's quick & easy.

MS Excel ...total hrs worked formula

P: 22
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


Expand|Select|Wrap|Line Numbers
  1. Day    Time In    Time Out    Time In    Time Out    Total Hrs
  2. mon    9:00    13:00    14:00    17:00    8:00
  3. tue    9:00    13:00    14:00    17:00    8:00
  4. wed    9:00    13:00    14:00    17:00    8:00
  5. thu    9:00    13:00    14:00    17:00    8:00
  6. fri    9:00    13:00    14:00    17:00    8:00
  7.             Week Total              [need formula or code]
Feb 13 '07 #1
Share this Question
Share on Google+
11 Replies


ronverdonk
Expert 2.5K+
P: 4,258
Why don't you use the standard Excel SUM() function, whereby you sum the previous 5 cells?

Ronald
Feb 13 '07 #2

Ganon11
Expert 2.5K+
P: 3,652
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,


Expand|Select|Wrap|Line Numbers
  1. Day    Time In     Time Out   Time In    Time Out    Total Hrs
  2. mon    9:00     13:00       14:00        17:00        8:00
  3. tue    9:00     13:00       14:00        17:00        8:00
  4. wed    9:00     13:00       14:00    17:00        8:00
  5. thu    9:00     13:00       14:00    17:00        8:00
  6. fri    9:00     13:00       14:00        17:00        8:00
  7.             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.
Feb 13 '07 #3

bartonc
Expert 5K+
P: 6,596
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


Expand|Select|Wrap|Line Numbers
  1. Day    Time In    Time Out    Time In    Time Out    Total Hrs
  2. mon    9:00    13:00    14:00    17:00    8:00
  3. tue    9:00    13:00    14:00    17:00    8:00
  4. wed    9:00    13:00    14:00    17:00    8:00
  5. thu    9:00    13:00    14:00    17:00    8:00
  6. fri    9:00    13:00    14:00    17:00    8:00
  7.             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.
Expand|Select|Wrap|Line Numbers
  1. =IF(A13=A14;"";G13)
  2. =IF(H13="";"";(H13*17)+K13)
My columns look something like this
Expand|Select|Wrap|Line Numbers
  1. Day    Time In    Time Out    Break    Project    Total Hrs
  2. 1/1/07    9:00    11:30            2.5
  3. 1/1/07    11:30    17:00    1        4.5
  4. 1/2/07    9:00    17:00    1        7
  5.  
  6.             Week Total              [need formula or code]
  7. =SUM(I13:I158)+E7
  8.  
I guess no one noticed that 9 to 5 minus one doesn't equal 8 hours.
Feb 13 '07 #4

bartonc
Expert 5K+
P: 6,596
Sorry, time (5 min.) to edit ran out.
Expand|Select|Wrap|Line Numbers
  1. =IF(NOT(E13="");((E13-D13)*24)-F13;"")
  2. =IF(A13=A14;"";G13)
  3. =IF(H13="";"";(H13*17)+K13)
My columns look something like this
Expand|Select|Wrap|Line Numbers
  1. Day    Project    Time In    Time Out    Break    Hours    Day hours    Total $
  2. 1/1/07        9:00    11:30        2.5
  3. 1/1/07        11:30    17:00    1    4.5    7
  4. 1/2/07        9:00    17:00    1    7    7
  5.  
  6.             Week Total              [need formula or code]
  7. =SUM(I13:I158)+E7
  8.  
I guess no one noticed that 9 to 5 minus one doesn't equal 8 hours.
Feb 13 '07 #5

P: 22
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
Feb 13 '07 #6

P: 22
Sorry, time (5 min.) to edit ran out.
Expand|Select|Wrap|Line Numbers
  1. =IF(NOT(E13="");((E13-D13)*24)-F13;"")
  2. =IF(A13=A14;"";G13)
  3. =IF(H13="";"";(H13*17)+K13)
My columns look something like this
Expand|Select|Wrap|Line Numbers
  1. Day    Project    Time In    Time Out    Break    Hours    Day hours    Total $
  2. 1/1/07        9:00    11:30        2.5
  3. 1/1/07        11:30    17:00    1    4.5    7
  4. 1/2/07        9:00    17:00    1    7    7
  5.  
  6.             Week Total              [need formula or code]
  7. =SUM(I13:I158)+E7
  8.  
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
Feb 13 '07 #7

bartonc
Expert 5K+
P: 6,596
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.
Feb 13 '07 #8

P: 22
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
Feb 14 '07 #9

bartonc
Expert 5K+
P: 6,596
What's wrong with
Expand|Select|Wrap|Line Numbers
  1. (E13-D13)*24
?
Feb 14 '07 #10

P: 22
What's wrong with
Expand|Select|Wrap|Line Numbers
  1. (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
Feb 14 '07 #11

bartonc
Expert 5K+
P: 6,596
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.
Feb 14 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.