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..

 

[MrTrent]

OK, so I have been messing about with my sheet, I haven't yet tried to implement the look up, as I am to tired and unsure how it works... However whilst I have been on my project it has given me some ideas.. I just now need to know how to execute it..

http://1drv.ms/1HcXNJj

On my first sheet "Overview" I have made the end Columns be "Contract Type & End Date"... I have currently made it pull data to what I know is the easiest way with '=Monday!C=J3' etc.. The unfortunate part about that is, it also copies the "Permanent Contract" cells as well.. So this is where I have thought, unfortunately I am good at thinking about this stuff, just not good at knowing how to do a formula...

Is it possible to some how make say Cell "Y3" on the Overview sheet link up with that persons name on the Contracts Work Book, pull the "Contract Type" which is like N4 as an example... Then it also reads the "End Date" on the contract sheet, which puts the date into Cell "Z3", but what it also acknowledges is, that if it is a Permanent Contract, the end date will be N/A.

Then once it gathers all this information, it then reverts it back to the Daily Sheets in the last column... But in that case if it is Permanent it will type that, as it is already..

Might be far fetched, and possibly will require a Macro.. Which is more joy for me I suppose..

 

[gaz_chops]

This is what I did earlier, sounds like what you say you want. To the right is a simple table with Perm/Contract, next to your Contract Ends you can see the new data shows if the contract date has expired and they are perm it says perm, else shows the Cont Date.

[TABLE="width: 961"]
<tbody>[TR]
[TD]NAME[/TD]
[TD="colspan: 3"]HOURS[/TD]
[TD]AREA[/TD]
[TD]BREAKS[/TD]
[TD]CHANGES[/TD]
[TD]CONTRACT ENDS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Perm[/TD]
[TD]Contract[/TD]
[/TR]
[TR]
[TD]Glenda[/TD]
[TD]6:00PM[/TD]
[TD]-[/TD]
[TD]11:00PM[/TD]
[TD]R.P[/TD]
[TD]15mins[/TD]
[TD][/TD]
[TD]27/Sep/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]27 September 2015[/TD]
[TD][/TD]
[TD]Glenda[/TD]
[TD][/TD]
[TD]27/Sep/2015[/TD]
[/TR]
[TR]
[TD]Tiff[/TD]
[TD]6:00PM[/TD]
[TD]-[/TD]
[TD]11:00PM[/TD]
[TD]Toys/Sport[/TD]
[TD]00:00[/TD]
[TD][/TD]
[TD]27/Sep/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]27 September 2015[/TD]
[TD][/TD]
[TD]Tiff[/TD]
[TD][/TD]
[TD]27/Sep/2015[/TD]
[/TR]
[TR]
[TD]Jay[/TD]
[TD]6:00PM[/TD]
[TD]-[/TD]
[TD]11:00PM[/TD]
[TD]HHC/Small Apps[/TD]
[TD]00:00[/TD]
[TD][/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[TD][/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[TD]Jay[/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jarrad[/TD]
[TD]6:00PM[/TD]
[TD]-[/TD]
[TD]10:00PM[/TD]
[TD]Confec/Stat[/TD]
[TD]00:00[/TD]
[TD][/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[TD][/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[TD]Jarrad[/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tasha[/TD]
[TD]6:00PM[/TD]
[TD]-[/TD]
[TD]10:00PM[/TD]
[TD]DIY/Camp/Pets[/TD]
[TD]00:00[/TD]
[TD]Away From Work[/TD]
[TD]Awaiting Return[/TD]
[TD][/TD]
[TD][/TD]
[TD]Awaiting Return[/TD]
[TD][/TD]
[TD]Tasha[/TD]
[TD][/TD]
[TD]Awaiting Return[/TD]
[/TR]
[TR]
[TD]Replace[/TD]
[TD]6:00PM[/TD]
[TD]-[/TD]
[TD]11:00PM[/TD]
[TD]HBA/Nursery[/TD]
[TD]00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD]r[/TD]
[TD][/TD]
[TD]No Contract[/TD]
[TD][/TD]
[TD]Replace[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B.J.[/TD]
[TD]6:00PM[/TD]
[TD]-[/TD]
[TD]11:00PM[/TD]
[TD]Manch/*******[/TD]
[TD]00:00[/TD]
[TD][/TD]
[TD]21/Jun/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]21 June 2015[/TD]
[TD][/TD]
[TD]B.J.[/TD]
[TD][/TD]
[TD]21/Jun/2015[/TD]
[/TR]
[TR]
[TD]Wendy[/TD]
[TD]6:00PM[/TD]
[TD]-[/TD]
[TD]11:00PM[/TD]
[TD]R.P[/TD]
[TD]00:00[/TD]
[TD]Sick[/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[TD]sect1[/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[TD]Wendy[/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]6:00PM[/TD]
[TD]-[/TD]
[TD]10:00PM[/TD]
[TD]DIY/Camp/Pets[/TD]
[TD]00:00[/TD]
[TD][/TD]
[TD]21/Jun/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]21 June 2015[/TD]
[TD][/TD]
[TD]Paul[/TD]
[TD][/TD]
[TD]21/Jun/2015[/TD]
[/TR]
[TR]
[TD]Justin[/TD]
[TD]2:00PM[/TD]
[TD]-[/TD]
[TD]10:00PM[/TD]
[TD]Backdock[/TD]
[TD]01:00[/TD]
[TD][/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[TD][/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[TD]Justin[/TD]
[TD]Permanent Contract[/TD]
[TD]14/Apr/2015[/TD]
[/TR]
[TR]
[TD]Aimee[/TD]
[TD]6:00PM[/TD]
[TD]-[/TD]
[TD]10:00PM[/TD]
[TD]R.P[/TD]
[TD]00:00[/TD]
[TD]Contracted - Wendy's Shift[/TD]
[TD]27/Sep/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]27 September 2015[/TD]
[TD][/TD]
[TD]Aimee[/TD]
[TD][/TD]
[TD]27/Sep/2015[/TD]
[/TR]
[TR]
[TD]Coco[/TD]
[TD]7:00PM[/TD]
[TD]-[/TD]
[TD]11:00PM[/TD]
[TD]R.P[/TD]
[TD]00:00[/TD]
[TD][/TD]
[TD]27/Sep/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]27 September 2015[/TD]
[TD][/TD]
[TD]Coco[/TD]
[TD]Permanent Contract[/TD]
[TD]27/Sep/2015[/TD]
[/TR]
[TR]
[TD]Renee[/TD]
[TD]4:30PM[/TD]
[TD]-[/TD]
[TD]9:30PM[/TD]
[TD]Planograms[/TD]
[TD]00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[TD]Renee[/TD]
[TD]Permanent Contract[/TD]
[TD]23/Mar/2015[/TD]
[/TR]
[TR]
[TD]Glenn[/TD]
[TD]3:00PM[/TD]
[TD]-[/TD]
[TD]11:00PM[/TD]
[TD]Confec/Stat[/TD]
[TD]01:00[/TD]
[TD][/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[TD][/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[TD]Glenn[/TD]
[TD]Permanent Contract[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MrTrent]

Yeah that is basically what I am trying to get... Then be able to have it show on the Overview Sheet to an extent also...

I have also thought of a simple way, however this way means I would have to pay full attention, and that would be to just have the last column on the overview unlocked, and edit the dates etc manually... But if it does come to an end, I fear I will not revert it etc... Which can cause confusion.

 

[MrTrent]

Well I still haven't been able to work a better way to do the end dates of contracts etc especially if they have an existing perm contract... But putting that aside for now, I have been informed that my guys will have to work 1 weekend in a month... This would be apart of the contract...
How would I be able to make this show on that specific Saturday for an associate? What is my leeway for this?
Like would I have to make a calendar type data base for this?
For an example:
Glenda may have to work the 3rd weekend from today... Her day off from this may be Thursday..
Paul might have to have the same weekend off, but his day off during the week might be Tuesday etc

 

[MrTrent]

Also on my workbook now, in the data section I have created another 1 columns with the staff members running down.. A nightfill & recovery column.. This is so if I have recruited someone else for the job I just have to change the name

 

[Michigan01]

Function MySplitter(InputString As String, WhatIsTheCharacter As String, WhatOcuurence As Integer) As String


Dim WrdArray() As String
On Error Resume Next
WrdArray = Split(InputString, WhatIsTheCharacter, 3)
MsgBox WrdArray(WhatOcuurence)


End Function