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]

Sorry, in each days sheet, in say column M, enter the above formula, then use that column in your sum on sheet 1, like

=((D23-B23)+(G23-E23)+(J23-H23)+(M23-K23)+(P23-N23)+(S23-Q23)+(V23-T23))-((Monday!M34)-(Tuesday!M34)-(Wednesday!M34)-(Thursday!M34)-(Friday!M34)-(Saturday!M34)-(Sunday!M34))

Ahhh THANK YOU!!! You Are A CHAMP!!!!!

That worked perfectly... Thanking you..

 

[MrTrent]

Is there a way to make it say Hour or Mins afterwards? But still act as a number... Similar to AM/PM? I have been trying a few things, but seems to be more of something you would have to type in individually if you have say a Straight up hour, or if you know it is always going to be minutes...
But mine you can predict will stay one or the other.

 

[gaz_chops]

Is there a way to make it say Hour or Mins afterwards? But still act as a number... Similar to AM/PM? I have been trying a few things, but seems to be more of something you would have to type in individually if you have say a Straight up hour, or if you know it is always going to be minutes...
But mine you can predict will stay one or the other.

You're welcome, do you mean show Hrs or Mins for the breaks on the daily sheets?

 

[MrTrent]

You're welcome, do you mean show Hrs or Mins for the breaks on the daily sheets?

Yeah, so perhaps I might be able to still make it say " 1 Hour " or " 30 Mins " etc... But because it will be as a number, it will still calculate it correctly.. So if it said " 15 Mins " that will be reading it as " 0:15 "...

 

[gaz_chops]

It is doable, but because you're using it in a calculation it will be a bit convoluted!

 

[gaz_chops]

Thought of a way!

On your data sheet insert a column after breaks, for the breaks enter 1 Hour, 30 Mins etc, in the next column enter the values, as below.

[TABLE="width: 114"]
<tbody>[TR]
[TD]Break[/TD]
[TD]Break[/TD]
[/TR]
[TR]
[TD]1 Hour[/TD]
[TD]01:00[/TD]
[/TR]
[TR]
[TD]45 Mins[/TD]
[TD]00:45[/TD]
[/TR]
[TR]
[TD]30 Mins[/TD]
[TD]00:30[/TD]
[/TR]
[TR]
[TD]15 Mins[/TD]
[TD]00:15[/TD]
[/TR]
[TR]
[TD]00:00[/TD]
[TD]00:00[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

Change the formula on your daily sheets in col M to be (Data Col D is 1Hour etc, Col E is 1:00 etc)

=IF(H34>TIME(0,15,0),VLOOKUP(H34,Data!D2:E8,2,FALSE),0)

In column H of your daily sheets use 1 Hour, 30 Mins etc, it will lookup and return 01:00, 00:30 etc to use in your calculations.

 

[gaz_chops]

Oops made a mistake with the formula! Should be

=IF(VLOOKUP(H34,Data!$D$2:$E$8,2,FALSE)>TIME(0,15,0),VLOOKUP(H34,Data!$D$2:$E$8,2,FALSE),0)

 

[MrTrent]

Thought of a way!


Change the formula on your daily sheets in col M to be (Data Col D is 1Hour etc, Col E is 1:00 etc)

=IF(H34>TIME(0,15,0),VLOOKUP(H34,Data!D2:E8,2,FALSE),0)

In column H of your daily sheets use 1 Hour, 30 Mins etc, it will lookup and return 01:00, 00:30 etc to use in your calculations.

Well done.. This too also works... Thank you very much.. I think that has about completed my sheet... Now I just have to fight battles with my printer, as it doesn't like to print it landscaped and full page.. To the left =/ Even though it is suppose to be in the middle...

 

[gaz_chops]

One thing, change the data sheet as below, not sure you need 00:00 & - but if it's not a value you will get an error. Also, make sure you use the last formula i posted, i changed it slightly.

Break	Break
1 Hour	01:00
45 Mins	00:45
30 Mins	00:30
15 Mins	00:15
00:00	00:00
-	00:00

<tbody>
</tbody>

=IF(VLOOKUP(H34,Data!$D$2:$E$8,2,FALSE)>TIME(0,15,0),VLOOKUP(H34,Data!$D$2:$E$8,2,FALSE),0)

 

[MrTrent]

One thing, change the data sheet as below, not sure you need 00:00 & - but if it's not a value you will get an error. Also, make sure you use the last formula i posted, i changed it slightly.

Yeah I just figured out that I needed to have it all aligned next to each other in the different columns..
Thanks again!!