Auto remove hours

[Endorphin]

Hi All,

I've been pulling my hair out to try and find a solution to my problem.
I am trying to automate my time sheet...

One column has the basic hours I am rostered to work in.
One column total hours worked in
one column has the additional overtime hours in

I work shifts, so each day has 3 sections to it:
07:00-14:00
14:00-22:00
22:00-07:00

Here is the problem:
If I am supposed to work 0700-1400 but they change my shift to say 1400-2200 I have to show what shift I was supposed to work but crossed out as well as the shift I actually worked.
So when I total up the hours worked in that column it would have an extra 8 hours in the total.
Is there a way to automatically lose the additional hours?
I have tried using the difference between the rostered + overtime and taking that from the total but keep getting a circular formula.

Its very hard to explain without actually seeing it but I am unable to attach the excel file.

any help appreciated.

 

[ChrisM]

sounds like you need one additional column, holding either "W" or "N" for work or no-work.

Now when you total your hours, do a SUMIF and only sum "W"s.

 

[Endorphin]

Thanks for the reply Chris,
The problem I have is I am not allowed to change the layout of the time sheet, it has been created by our HR department.

Thanks for your quick reply.