MS Excel ...total hrs worked formula

**ronverdonk** · Feb 13 '07, 06:45 PM

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

Ronald

**Ganon11** · Feb 13 '07, 06:49 PM

Originally posted by 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

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

Code:

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** · Feb 13 '07, 08:30 PM

Originally posted by 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

Code:

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.

Code:

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

My columns look something like this

Code:

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** · Feb 13 '07, 08:41 PM

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

Code:

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

My columns look something like this

Code:

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** · Feb 13 '07, 11:25 PM

Originally posted by ronverdonk

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** · Feb 13 '07, 11:31 PM

Originally posted by bartonc

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

Code:

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

My columns look something like this

Code:

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** · Feb 13 '07, 11:48 PM

Originally posted by Lloydm

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** · Feb 14 '07, 01:43 AM

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.66666666 7)

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** · Feb 14 '07, 04:08 AM

What's wrong with

Code:

(E13-D13)*24

?

**Lloydm** · Feb 14 '07, 04:08 PM

Originally posted by bartonc

What's wrong with

Code:

(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)*2 4/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** · Feb 14 '07, 10:38 PM

Originally posted by Lloydm

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)*2 4/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.

MS Excel ...total hrs worked formula

MS Excel ...total hrs worked formula

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment