Excel Time Subtraction Help.

[MrTrent]

Hi,
I have created a sheet which is almost like a time sheet. What I need to do is be able to subtract allocated breaks. But I don't need it to do it to all of them...

This is my current formula which gives me most of what I want...

{=((D3-B3)+(G3-E3)+(J3-H3)+(M3-K3)+(P3-N3)+(S3-Q3)+(V3-T3)-(Monday!H3)-(Tuesday!H3)-(Wednesday!H3)-(Thursday!H3)-(Friday!H3)-(Saturday!H3)-(Sunday!H3)}

I don't know much about Excel, so sorry if it is an incorrect formula or could be simplified..

In the cell on the "Days Sheets" I currently have it set to 0:30 0:45 1:00 ... I want it to have 0:15, but I don't want that to be subtracted..
Basically what the sheet has is a bunch of times, and if they work over 5 hours, they have to take out 30mins or more depending on the length of the shift..

 

[gaz_chops]

You're welcome have a great weekend.

 

[MrTrent]

You too mate... Unfortunately I have to attend work in less that 12 hours, so it isn't going to be a good weekend, lol.

 

[MrTrent]

Just another quick question, what would be the ideal way to enter the contract end date?

I currently have to enter it in on Monday... Is there a formula that can pick this? And or, if the associate has a permanent contract, but given a temp contract is there something better I can do? I would like for it to revert back to the permanent contract after the date is up on it...

 

[gaz_chops]

I think you would need to another sheet with the details for each person - Perm or not and Contract Dates, could then do a lookup, if there is a current contract date use that, if not check if they are perm or not!

Hope that makes sense!!

Gaz

 

[MrTrent]

I think I kind of understand... What I also have is another sheet which has everyone's contracts... I could perhaps use this to my advantage?

 

[gaz_chops]

Yes, if you already have a sheet with the Perm/Contract details it can prob be used. You could create a formula under the "Contract Ends" date on your Daily sheets, that checks if the date is less than or equal to Today, if so check the Perm details.

If you get stuck let me know, you could upload a new version with the contract sheet and i'll take look.

Gaz

 

[MrTrent]

Yeah I most likely will need a hand as I don't really know formula's as such... I'll try and do a demo sheet as I need to watch what I share for company purposes

 

[gaz_chops]

This is formula I just did, seems to work the way you want, just need to adapt it for the Contracts Sheet.

=IF(VLOOKUP(C3,$N$3:$P$16,3,0)>TODAY(),VLOOKUP(C3,$N$3:$P$16,3,0),IF(VLOOKUP(C3,$N$3:$P$16,2,0)="Permanent Contract","Permanent Contract","No Contract"))

 

[MrTrent]

I do have this, which was a part project...
This I have somewhat completed... But, instead of doing it on the Contract Sheets, I did it on another sheet which I use on a daily basis, which I had entered in all the data manually.

https://docs.google.com/spreadsheets/d/1qiRJY5xMjcStyAtRAyWG_-fuuw3goaafI3TL5lcvIGY/edit?usp=sharing

Don't mind the "Overview" Sheet, as that was when I first started to play around.. The other sheets are somewhat, what my contracts look like, however I might have like 30 or more...

 

[MrTrent]

This is formula I just did, seems to work the way you want, just need to adapt it for the Contracts Sheet.

=IF(VLOOKUP(C3,$N$3:$P$16,3,0)>TODAY(),VLOOKUP(C3,$N$3:$P$16,3,0),IF(VLOOKUP(C3,$N$3:$P$16,2,0)="Permanent Contract","Permanent Contract","No Contract"))

So where would I put this? And how would I be able to adapt it to my Contract Sheets?