437,708 Members | 2,095 Online 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 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] Feb 13 '07 #1
11 Replies

 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

 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 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. Feb 13 '07 #3

 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 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. Expand|Select|Wrap|Line Numbers =IF(A13=A14;"";G13) =IF(H13="";"";(H13*17)+K13) My columns look something like this Expand|Select|Wrap|Line Numbers 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. Feb 13 '07 #4

 Expert 5K+ P: 6,596 Sorry, time (5 min.) to edit ran out. Expand|Select|Wrap|Line Numbers =IF(NOT(E13="");((E13-D13)*24)-F13;"") =IF(A13=A14;"";G13) =IF(H13="";"";(H13*17)+K13) My columns look something like this Expand|Select|Wrap|Line Numbers 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. 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 =IF(NOT(E13="");((E13-D13)*24)-F13;"") =IF(A13=A14;"";G13) =IF(H13="";"";(H13*17)+K13) My columns look something like this Expand|Select|Wrap|Line Numbers 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 Feb 13 '07 #7

 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

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

 P: 22 What's wrong with Expand|Select|Wrap|Line Numbers (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

 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 