Add 30 mins to a time going bonkers

D

Diesel9a1

New Member
Joined
Feb 12, 2015
Messages
37
Ok, so hopefully the table below explains the issue - the 38.00 in column Q is the problem. The issue seems to occur if I have a none on the hour start time with and try to add 30 minutes:
(HOUR(P2+TIME(0,30,0))+MINUTE(P2+TIME(0,30,0)))


[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD="align: center"]L
Start
[/TD]
[TD="align: center"]M
Finish[/TD]
[TD="align: center"]N
(Deduct 30' Lunch)[/TD]
[TD="align: center"]O
(Decimal Shift)[/TD]
[TD="align: center"]P
(T'Sheet Hours)
[/TD]
[TD]
Q
(Invoice Qty)​
[/TD]
[/TR]
[TR]
[TD="align: center"]07:00[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]8.50[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]9.00[/TD]
[/TR]
[TR]
[TD="align: center"]07:00[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]8.50[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]8.50[/TD]
[/TR]
[TR]
[TD="align: center"]07:30[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]8.00
[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]38.00[/TD]
[/TR]
[TR]
[TD="align: center"]07:30[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]8.00[/TD]
[/TR]
[TR]
[TD="align: center"]21:00[/TD]
[TD="align: center"]06:00[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]8.50[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]9.00[/TD]
[/TR]
[TR]
[TD="align: center"]21:00[/TD]
[TD="align: center"]06:00[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]8.50[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]8.50[/TD]
[/TR]
[TR]
[TD="align: center"]21:30[/TD]
[TD="align: center"]06:00[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]38.00[/TD]
[/TR]
[TR]
[TD="align: center"]21:30[/TD]
[TD="align: center"]06:00[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]8.00[/TD]
[/TR]
</tbody>[/TABLE]

O Column = P2*24

P Column = ((M2-L2+(M2<L2))-TIME(0,30,0))

Q Column = IF(N2="Y",(P2*24),(HOUR(P2+TIME(0,30,0))+MINUTE(P2+TIME(0,30,0))))



Thanks in advance folks.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Diesel9a1 said:
Ok, so hopefully the table below explains the issue - the 38.00 in column Q is the problem. The issue seems to occur if I have a none on the hour start time with and try to add 30 minutes:
(HOUR(P2+TIME(0,30,0))+MINUTE(P2+TIME(0,30,0)))[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

O Column = P2*24

P Column = ((M2-L2+(M2<l2))-time(0,30,0))

Q Column = IF(N2="Y",(P2*24),(HOUR(P2+TIME(0,30,0))+MINUTE(P2+TIME(0,30,0))))
Thanks in advance folks.
Click to expand...

Unfortunately, firstly one of your formulae is not complete. Secondly what do you mean by "a none"?

I've highlighted in red in the quote both of those issues.
</l2))-time(0,30,0))
 
Upvote 0
In addition to what stated by BrianJN1, you should also explain what are you trying to calculate and probably we will find a more direct path.

Bye
 
Upvote 0
Tony’s answer works for me and is much simpler and shorter but, FWIW, the problem with yours i# that you need to divide the MINUTE part by 60
 
Upvote 0
barry houdini said:
Tony’s answer works for me and is much simpler and shorter but, FWIW, the problem with yours i# that you need to divide the MINUTE part by 60
Click to expand...

The difference between an Expert and a Power User.

I attempted to amend the existing formula but gave up after a few minutes when I realised existing data could be manipulated to suit purpose, like Barry said "simple".
 
Last edited:
Upvote 0
I've completed the PART formula from original post here in this QUOTE:

Diesel9a1 said:
Ok, so hopefully the table below explains the issue - the 38.00 in column Q is the problem. The issue seems to occur if I have a none on the hour start time with and try to add 30 minutes:
(HOUR(P2+TIME(0,30,0))+MINUTE(P2+TIME(0,30,0)))


[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD="align: center"]L
Start[/TD]
[TD="align: center"]M
Finish[/TD]
[TD="align: center"]N
(Deduct 30' Lunch)[/TD]
[TD="align: center"]O
(Decimal Shift)[/TD]
[TD="align: center"]P
(T'Sheet Hours)
[/TD]
[TD]
Q
(Invoice Qty)​
[/TD]
[/TR]
[TR]
[TD="align: center"]07:00[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]8.50[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]9.00[/TD]
[/TR]
[TR]
[TD="align: center"]07:00[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]8.50[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]8.50[/TD]
[/TR]
[TR]
[TD="align: center"]07:30[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]38.00[/TD]
[/TR]
[TR]
[TD="align: center"]07:30[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]8.00[/TD]
[/TR]
[TR]
[TD="align: center"]21:00[/TD]
[TD="align: center"]06:00[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]8.50[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]9.00[/TD]
[/TR]
[TR]
[TD="align: center"]21:00[/TD]
[TD="align: center"]06:00[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]8.50[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]8.50[/TD]
[/TR]
[TR]
[TD="align: center"]21:30[/TD]
[TD="align: center"]06:00[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]38.00[/TD]
[/TR]
[TR]
[TD="align: center"]21:30[/TD]
[TD="align: center"]06:00[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]8.00[/TD]
[/TR]
</tbody>[/TABLE]

O Column = P2*24

P Column = ((M2-L2+(M2<L2))-TIME(0,30,0))<l2))-time(0,30,0))

Q Column = IF(N2="Y",(P2*24),(HOUR(P2+TIME(0,30,0))+MINUTE(P2+TIME(0,30,0))))



Thanks in advance folks.
Click to expand...


"</l2))-time(0,30,0))
I have a none on the hour start time"
bad English / grammar, but I couldn't think what else to call it
i.e.
on the hour = 21:00, 22:00, 03:00.
none on the hour = 22:03, 22:22, 23:55 etc etc


"what are you trying to calculate" = The total time in decimal (column Q) from Start Time to Finish time and deducting 30 minutes if the "Deduct 30' Lunch" = "Y"

Times are worked shift times in that they also go past midnight so this is why the formula in column Q is a bit complex. If midnight wasn't involved, then a simple finish minus start would suffice. The above all works fine apart from when a "none on the hour start time" is used.
 
Upvote 0
Try this. I decided to ignore your Decimal Shift.
I believe this does what you set out to do:

Excel 2013/2016
LMNOPQ
1StartFinish(Deduct 30' Lunch)(Decimal Shift)(T'Sheet Hours)(Invoice Qty)
27:0016:00N8:309:00
37:0016:00Y8:308:30
47:3016:00N8:008:30
57:3016:00Y8:008:00
621:006:00N8:309:00
721:006:00Y8:308:30
821:306:00N8:008:30
921:306:00Y8:008:00
Sheet1
Cell Formulas
RangeFormula
P2=IF(M20,30,0),M2-L2-TIME(0,30,0))
Q2=IF(N2="N",P2+TIME(0,30,0),P2)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
R
  • Solved
Adding Hours & Minutes to Dates & Times w/in Working Hours
2
Replies
11
Views
614
Asbestos_Jen
Asbestos_Jen
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
192
Tara_A_Data
T
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
270
tezza
tezza
A
  • Question Question
Add a specified number of working hours to a date & time, given working day start, end and break times and holidays
Replies
2
Views
857
AquaBuoy
A

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

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