If type X then CellA = 1, if type X2 then CellA = .5 w/ SUM

**adamsurpren** · 01-08-2012, 08:20 AM

Hello!

I am working on an overtime sheet for my office and was wondering if someone could help. Please review the attached sheet below. Hopefully it's not too confusing!

I would like to make it so that if someone types/selects (not sure which is easier) 'OT' in a cell under the time frames, it will change the corresponding 'OT Hours' cell to reflect a 1. Also, if 'OT2' (something similar) is typed a .5 will be added to the 'OT Hours' cell total. Subsequent 'OT's or 'OT2's in other times for that same day will also total as well. If someone types N, it will not change.

I would also prefer if whenever 'OT' is typed it will change to a red color, while 'N' stays black.

I am hoping this or something similar can be done, thank for reading and helping me!

Overtime Schedule.xlsx

-Adam

**martindwilson** · 01-08-2012, 09:15 AM

do you mean in c4
=COUNTIF(D3:Q3,"ot")+COUNTIF(D3:Q3,"ot2")*1.5

**adamsurpren** · 01-08-2012, 09:26 AM

Thanks for the reply martindwilson,

That formula does work. I changed the *1.5 to .5 and it fits exactly as I needed it to. Thank you sir!

One more question though, is there a way to make some sort of macro so 'OT' and 'OT2' always appear red and 'N' black? Some people do not have normal hours at 7, so OT to them could be later than my sheet shows.

**Alan Kettlewell** · 01-09-2012, 04:21 AM

If you have Excel 2007 or later then you can specify the cell formatting using 'Conditional Formatting .. Highlight Cell Rules' ... So if the cell contains the text "OT" then make format the font as RED, and so on.

Regards
Alan

Edit - Sorry Adam, I've just noticed you have Excel version 2003 - apologies. Alan

**adamsurpren** · 01-09-2012, 10:57 AM

Thanks for trying Alan Kettlewell,

Unfortunately state agencies take awhile to upgrade their programs so I'm going to be stuck with 2003 for awhile.

Is there a solution to my problem for this version of excel?

**Fotis1991** · 01-09-2012, 11:08 AM

Hi

...One more question though, is there a way to make some sort of macro so 'OT' and 'OT2' always appear red and 'N' black? Some people do not have normal hours at 7, so OT to them could be later than my sheet shows.

If i understoond well your point, you can do this in Excel2003.

Hope to helps you

**adamsurpren** · 01-09-2012, 02:35 PM

Thanks for the reply Fotis1991,

That looks okay. Ideally, I was looking to change the actual text color, not a highlight color - but this works. Thank you.

However, I cannot figure out how to apply this format to my document. Can you please advise me on how to use this?