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.


any help appreciated.

[masteff]

You could add another column for a "schedule change" flag and then use SUMIF() to sum where it's not flagged. (what does your "status code" column get used for? could you add a unique code to enter when it's a schedule change?). For example, if you used an "x" in status code as a flag, your formula in cell P25 might be: =SUMIF(H4:I24,"<>X",P4:Q24)

Hmm... you could then use conditional formatting to add the strikethru and red background, thus effectively automating it based on that flag.

[Endorphin]

Hi Masteff,
Thanks for your reply

Unfortunatley all the columns are used at some point in time and we are not allowed to change the timesheet format which is dam annoying.