Text Values

[Susan Smith]

Hello,

Can anybody help, I'm after making a spreadsheet in Excel to record
times for individuals, for example if I typed in 'early shift' with the
value of 10 hours, after 'noon shift' 8 hours as well as 'late' shift at
12 hours...etc, the total values would all show in a totals cell for
that person.

I would appreciate any help with the above.

Love,

Susan


***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Frank Kabel]

Hi Susan
one way:
use a helper column which transforms this text string into a value.
e.g. if you enter these 'shift types' in column B you may enter the
following in column C
=IF(B1<>"",VLOOKUP(B1,'shift_types'!$A$1:$B$10,2,0),"")
and copy down

where shift_type is a separate worksheet with your shift_types in
column A and the associated time in column B

To get the aggreagtes you have several ways:
1. Consider using a pivot table on this data. See
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
to get startet with them

2. formula approach: If your person name is stored in column A you may
use the following:
=SUMIF(A1:A1000,"name of person",C1:C1000)
and format this cell with the custom format
[hh]:mm