Time subtraction

[Wolfin2010]

What I'm trying to do is, if total hours are less than 4 hrs give me the answer 4:00 otherwise subtract D-C

=IF(OR(INDIRECT("D"&ROW())="",INDIRECT("C"&ROW())=""),"",IF((INDIRECT("D"&ROW())-INDIRECT("C"&ROW())*24)>"4","4:00",TIME(HOUR(INDIRECT("D"&ROW())),MROUND(MINUTE(INDIRECT("D"&ROW())),15),0)-TIME(HOUR(INDIRECT("C"&ROW())),MROUND(INDIRECT("C"&ROW()),15),0)))

problem I'm getting is if the hrs are less than 4, I get exact answer not 4:00

=IF(OR(INDIRECT("D"&ROW())="",INDIRECT("C"&ROW())=""),"",IF((INDIRECT("D"&ROW())-INDIRECT("C"&ROW())*24)<"4","4:00",TIME(HOUR(INDIRECT("D"&ROW())),MROUND(MINUTE(INDIRECT("D"&ROW())),15),0)-TIME(HOUR(INDIRECT("C"&ROW())),MROUND(INDIRECT("C"&ROW()),15),0)))

I get answer 4:00 regardless the answer.

Any suggestions?

 

[jasonb75]

Try

=MAX(D2-C2,"04:00:00"+0)

formatted as [h]:mm:ss

 

[Wolfin2010]

Try

=MAX(D2-C2,"04:00:00"+0)

formatted as [h]:mm:ss

Thanks for the reply

=MAX(INDIRECT("D"&ROW())-INDIRECT("C"&ROW()),"4:00")

Does work but it does not round up the minutes so I tried

=IF(MAX(INDIRECT("D"&ROW())-INDIRECT("C"&ROW()),"4:00"),"4:00",TIME(HOUR(INDIRECT("D"&ROW())),MROUND(MINUTE(INDIRECT("D"&ROW())),15),0)-TIME(HOUR(INDIRECT("C"&ROW())),MROUND(MINUTE(INDIRECT("C"&ROW())),15),0))

Which keeps on giving me the same answer 4:00

Time format is [h]:mm

 

[jasonb75]

Why are you using INDIRECT()? That is causing the formula to break.

edit:- ignore that, I misread what you were trying to do, although use of indirect should not be necessary.

=MAX(MROUND(D2-C2,1/1440),"04:00:00"+0)

 

[Wolfin2010]

I will be adding the formula in Name Manager for that I'm using Indirect

 

[jasonb75]

Makes sense now

=MAX(MROUND(INDIRECT(RC4,0)-INDIRECT(RC3,0),1/1440),"04:00:00"+0)

 

[Wolfin2010]

Makes sense now

=MAX(MROUND(INDIRECT(RC4,0)-INDIRECT(RC3,0),1/1440),"04:00:00"+0)

Thank you very much it's providing the right value. one more question how can I modify formula so D2-C2 will give me the right value (6:45) not 4:00

C2	D2
24-11-2020 5:45:00 PM	25-11-2020 12:30:00 AM

 

[jasonb75]

There was a typo in my formula which might be causing the problem, I would have expected an error but the difference might be due to you having it in a named range. This will work correctly with either.

Book1
	C	D	E
2	24/11/2020 17:55	25/11/2020 00:30	06:35:00
Sheet1

Cell Formulas
Range	Formula
E2	E2	=MAX(MROUND(INDIRECT("RC4",0)-INDIRECT("RC3",0),1/1440),"04:00:00"+0)

 

[Wolfin2010]

Book1
	K
2	10:30
Sheet1

Cell Formulas
Range	Formula
G2:G3, K2:K3	G2	=MAX(TIME(HOUR(INDIRECT("D"&ROW())),MROUND(MINUTE(INDIRECT("D"&ROW())),15),0)-TIME(HOUR(INDIRECT("C"&ROW())),MROUND(MINUTE(INDIRECT("C"&ROW())),15),1/1440),"04:00:00"+0)
H2:H3	H2	=MAX(MROUND(D2-C2,1/1440),"04:00:00"+0)
I2:I3	I2	=IF(INT((D2-C2)*24)<4,"4:00",TIME(HOUR(D2),MROUND(MINUTE(D2),15),0)-TIME(HOUR(C2),MROUND(MINUTE(C2),15),0))
J2:J3	J2	=MAX(MROUND(INDIRECT("RC4",0)-INDIRECT("RC3",0),1/1440),"04:00:00"+0)
E2:E3	E2	=IF(OR(INDIRECT("C"&ROW())="",INDIRECT("D"&ROW())=""),"",INDIRECT("D"&ROW())-INDIRECT("C"&ROW()))

having problem with the minutes round up.

 

[jasonb75]

Something went wrong with your last post, the example sheet is not showing correctly. Should the minutes be rounding up or rounding to nearest?

If you always want to round up then you would need to use CEILING instead of MROUND.