Excel Time Subtraction Help.

MrTrent

Board Regular
Joined
Apr 10, 2015
Messages
54
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..
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi MrTrent,
Can you post a sample of your data, or upload to dropbox and post a link here.

Gaz
 
Upvote 0
Just a suggestion, on Sheet 1 why not show the Total Hours for each day & the breaks (if over 15 mins), like

Code:
[TABLE="width: 163"]
<tbody>[TR]
[TD]Associate[/TD]
[TD="colspan: 3"]Monday[/TD]
[/TR]
[TR]
[TD]Cassandra[/TD]
[TD]4:00[/TD]
[TD]-[/TD]
[TD]0:45[/TD]
[/TR]
[TR]
[TD]Christine[/TD]
[TD]4:00[/TD]
[TD]-[/TD]
[TD]1:00[/TD]
[/TR]
[TR]
[TD]Edith[/TD]
[TD]4:00[/TD]
[TD]-[/TD]
[TD]0:30[/TD]
[/TR]
[TR]
[TD]Dalton[/TD]
[TD]4:00[/TD]
[TD]-[/TD]
[TD]0:00[/TD]
[/TR]
[TR]
[TD]Trudi[/TD]
[TD]5:00[/TD]
[TD]-[/TD]
[TD]0:00[/TD]
[/TR]
</tbody>[/TABLE]

The formula for breaks could be a simple
=IF(Monday!H34>TIME(0,15,0),Monday!H34,0)

Then your formula in Sheet 1 W 23 could be
=SUM(B23,E23,H23,K23,N23,Q23,T23)-SUM(D23,G23,J23,M23,P23,S23,V23)
 
Upvote 0
The time frames are important, due to reconstructing a new layout for the Associates... So need to know what times and where etc...
 
Upvote 0
Just a suggestion, on Sheet 1 why not show the Total Hours for each day & the breaks (if over 15 mins), like

Code:
[TABLE="width: 163"]
<tbody>[TR]
[TD]Associate[/TD]
[TD="colspan: 3"]Monday[/TD]
[/TR]
[TR]
[TD]Cassandra[/TD]
[TD]4:00[/TD]
[TD]-[/TD]
[TD]0:45[/TD]
[/TR]
[TR]
[TD]Christine[/TD]
[TD]4:00[/TD]
[TD]-[/TD]
[TD]1:00[/TD]
[/TR]
[TR]
[TD]Edith[/TD]
[TD]4:00[/TD]
[TD]-[/TD]
[TD]0:30[/TD]
[/TR]
[TR]
[TD]Dalton[/TD]
[TD]4:00[/TD]
[TD]-[/TD]
[TD]0:00[/TD]
[/TR]
[TR]
[TD]Trudi[/TD]
[TD]5:00[/TD]
[TD]-[/TD]
[TD]0:00[/TD]
[/TR]
</tbody>[/TABLE]

The formula for breaks could be a simple
=IF(Monday!H34>TIME(0,15,0),Monday!H34,0)

Then your formula in Sheet 1 W 23 could be
=SUM(B23,E23,H23,K23,N23,Q23,T23)-SUM(D23,G23,J23,M23,P23,S23,V23)
Basically the layout I have is going to be used as a guide to change the structure. It is also an good overview of what contracts associates have... And can be useful for call ins and or extended shifts..

I can simply leave it as 0:00, but if I want the appearance of it to look good, it would be convenient to have it as 0:15 just to fill the gap... Otherwise I am sure if I did ;; at the end of h:mm it will make it disappear if it has nothing..
 
Upvote 0
Then I would add a "helper" column in each Days sheet, with the formula =IF(H34>TIME(0,15,0),H34,0)
to check if >15 mins, then use that in your formula on sheet 1, you can hide the column after if needed.
 
Upvote 0
Then I would add a "helper" column in each Days sheet, with the formula =IF(H34>TIME(0,15,0),H34,0)
to check if >15 mins, then use that in your formula on sheet 1, you can hide the column after if needed.

I don't really understand what you mean sorry

All I seem to get out of that is, if I enter it into a Cell, the Cell just goes to 0 or something.. or just 15... But it still takes that off the overall time on Sheet1... I need Sheet1 to not acknowledge anything until it is 0:30 or how higher.
 
Last edited:
Upvote 0
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))
 
Upvote 0
You will need to change your data validation sheet for Breaks, change 15mins to 00:15
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top