need formula for my spreadsheet

[Cheryl]

Hello,

I have an excel spreadsheet that I'm suppose to be tracking our
overtime, attendance, etc. for our department (110 employees).
If on a certain date a person works 2 hours overtime, can I type
'OT(meaning overtime) and then the number of hours worked say....OT3
and then on a different date type OT4 and then the formula should
calculate this to be OT worked = 7 hours?
Clear as mud right???
Thanks for your time............any efforts are GREATLY appreciated.

Cheryl

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=425551

 

[Fred Smith]

IF a1 has OT3 and a2 has OT4, this formula will add the two numbers:

=right(a1,1)+right(a2,1)

But it doesn't work if you ever have more than 9 hours of overtime, or ever
have fractional hours (eg, 3.5). If that's the case, post back with more
data.

PS. Do you have to enter the data in this peculiar way? Why not enter the
hours in a separate cell?

 

[Zstever]

I saw what Fred Smith wrote and wondered if there is a
way to do it with different types of numbers... OT3,
OT2.75, OT10.25, etc...

Assume your data is in cells C5 & C6 this formula will
work. It gets the length of the data in the cell,
subrtracts 2 from it, for the OT, and then adds the
number.

=RIGHT(C5,(LEN(C5)-2))+RIGHT(C6,(LEN(C6)-2))

Now, the cells must always have at least two characters
in the first two positions, OT for example, or this
formula will not work.

I also agree with Mr. Smith, it seems like it would be
much easier if you could put Overtime hours into a cell
of it's own.

Stever