Multiple functions in one cell
 

I am trying to build a spreadsheet so I can track my times at work. I am
having trouble trying to create a formula for 1 cell that will sum all the
days of the month, and add a 1 for every 8 hours of standby time example:
01/02/05 4 hours of standby, 01/03/05 2 hours of standby, 01/04/05 8 hours of
standby, 01/05/05 2 hours of standby, with the total being 16 I want that
cell to calculate too 2.
Thanks for any help you can provide!!
Kevin.

If you put your data into two columns, you're life will become lots easier.

And if you have your data already entered, maybe you can use Data|Text to
columns to separate it into those two columns.

Choose delimited by space.

Then you can just sum that second column and divide by 8.

But you could use a formula like:

=SUMPRODUCT(--MID(A1:A5,FIND(" ",A1:A5)+1,255))/8
(adjust the range to match your data (in both spots!).)

But you'll find excel lots easier to work with if you separate different fields
into different columns.

Kevin wrote:

I am trying to build a spreadsheet so I can track my times at work. I am
having trouble trying to create a formula for 1 cell that will sum all the
days of the month, and add a 1 for every 8 hours of standby time example:
01/02/05 4 hours of standby, 01/03/05 2 hours of standby, 01/04/05 8 hours of
standby, 01/05/05 2 hours of standby, with the total being 16 I want that
cell to calculate too 2.
Thanks for any help you can provide!!
Kevin.

--

Dave Peterson

Use sumif. it is in the help files, too.
Try this, these 3 line formulas are really one long formula each, the
formating here wrapped them. the result of the formula is above each one,
according to the sample input table below.
Note: use 'roundup' if you get a full "1" for less than 8h standby, use
'round' if you can round up 5 or more sby hours to the next "1" and
'rounddown' if they are a bunch of scrooges. lol

Overtime Rate 1.5 cell F5
Standby Conversion 8 cell F6

Time Charged rounded down 46
SUM(SUMIF($F$15:$G$35,"Regular",$G$15:$G$35),($F$5 *SUMIF($F$15:$G$35,"Overtime",$G$15:$G$35)),ROUNDD OWN(SUMIF($F$15:$G$35,"Standby",$G$15:$G$35)/$F$6,0))
Time Charged rounded off 46
SUM(SUMIF($F$15:$G$35,"Regular",$G$15:$G$35),($F$5 *SUMIF($F$15:$G$35,"Overtime",$G$15:$G$35)),ROUND( SUMIF($F$15:$G$35,"Standby",$G$15:$G$35)/$F$6,0))
Time Charged rounded up 47
SUM(SUMIF($F$15:$G$35,"Regular",$G$15:$G$35),($F$5 *SUMIF($F$15:$G$35,"Overtime",$G$15:$G$35)),ROUNDU P(SUMIF($F$15:$G$35,"Standby",$G$15:$G$35)/$F$6,0))
E15:E35 F15:F35 G15:G35
Day 1 Regular 8
Overtime 1
Standby 3
Day 2 Regular 8
Overtime
Standby 4
Day 3 Regular 8
Overtime 1
Standby
Day 4 Regular 8
Overtime
Standby 3
Day 5 Regular
Overtime
Standby 8
Day 6 Regular
Overtime
Standby 8
Day 7 Regular 8
Overtime
Standby 1


"Kevin" wrote:

I am trying to build a spreadsheet so I can track my times at work. I am
having trouble trying to create a formula for 1 cell that will sum all the
days of the month, and add a 1 for every 8 hours of standby time example:
01/02/05 4 hours of standby, 01/03/05 2 hours of standby, 01/04/05 8 hours of
standby, 01/05/05 2 hours of standby, with the total being 16 I want that
cell to calculate too 2.
Thanks for any help you can provide!!
Kevin.