Add 30 mins to a time going bonkers

[Diesel9a1]

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

L Start	M Finish	N (Deduct 30' Lunch)	O (Decimal Shift)	P (T'Sheet Hours)	Q (Invoice Qty)​
07:00	16:00	N	8.50	8:30	9.00
07:00	16:00	Y	8.50	8:30	8.50
07:30	16:00	N	8.00	8:00	38.00
07:30	16:00	Y	8.00	8:00	8.00
21:00	06:00	N	8.50	8:30	9.00
21:00	06:00	Y	8.50	8:30	8.50
21:30	06:00	N	8.00	8:00	38.00
21:30	06:00	Y	8.00	8:00	8.00

<tbody>
</tbody>

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.

 

[BrianJN1]

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

<tbody>
</tbody>

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.

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

 

[Anthony47]

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

 

[TonyUK72]

Q2 and copied down

=IF(N2="N",O2+0.5,O2)

 

[barry houdini]

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

 

[TonyUK72]

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

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

 

[Diesel9a1]

I've completed the PART formula from original post here in this QUOTE:

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

L Start	M Finish	N (Deduct 30' Lunch)	O (Decimal Shift)	P (T'Sheet Hours)	Q (Invoice Qty)​
07:00	16:00	N	8.50	8:30	9.00
07:00	16:00	Y	8.50	8:30	8.50
07:30	16:00	N	8.00	8:00	38.00
07:30	16:00	Y	8.00	8:00	8.00
21:00	06:00	N	8.50	8:30	9.00
21:00	06:00	Y	8.50	8:30	8.50
21:30	06:00	N	8.00	8:00	38.00
21:30	06:00	Y	8.00	8:00	8.00

<tbody>
</tbody>

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.

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

 

[BrianJN1]

Try this. I decided to ignore your Decimal Shift.
I believe this does what you set out to do:

Excel 2013/2016
	L	M	N	O	P	Q
1	Start	Finish	(Deduct 30' Lunch)	(Decimal Shift)	(T'Sheet Hours)	(Invoice Qty)
2	7:00	16:00	N		8:30	9:00
3	7:00	16:00	Y		8:30	8:30
4	7:30	16:00	N		8:00	8:30
5	7:30	16:00	Y		8:00	8:00
6	21:00	6:00	N		8:30	9:00
7	21:00	6:00	Y		8:30	8:30
8	21:30	6:00	N		8:00	8:30
9	21:30	6:00	Y		8:00	8:00
Sheet1

Cell Formulas
Range	Formula
P2	=IF(M20,30,0),M2-L2-TIME(0,30,0))
Q2	=IF(N2="N",P2+TIME(0,30,0),P2)