Excel 2010 - Help with making 'Standby' apply as 7.2 in formula - Possibly: Change Event (VBA)

[DMF]

Hi All,

This is my first official post so please bare with me.

PROBLEM:

I am trying to create a speadsheet which is a timesheet. I have two versions, one is a simple table that the guys put in their total daily hours or write 'Standby' when they are on standby i.e.

Mon 12
Tue 12
Wed Standby
Thu 12
Fri 12


On the other I have the hours split into Standard and Overtime using the min and max functions which works out a min standard day of 7.2 hrs and anything over as o/t. This all works fine.

The issue I'm having is that when they write 'Standby' I want it to return in my otherspreadsheet as 7.2 in the standard hours column. I think the answer is to write a change event on VBA but this is beyond me. Can anybody help write the change event formula and give me a step-by-step on how to apply it or suggest another way? (I have tried Define Name and it doesn't apply... as I want to further calculate the total hours etc)

Much appreciated!!!

[DMF]

Update:

I've taken a different angle and added a 'Standby hours' column which formulates as a COUNTIF times the hours per day