Auto remove hours

Endorphin

Board Regular
Joined
Oct 27, 2006
Messages
62
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.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

Board Regular
Joined
Oct 27, 2006
Messages
62
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,538
Messages
5,596,750
Members
414,097
Latest member
FaeFen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top