Formula problem

[Stef9910]

Hi all,

I am having difficulties with a formula that should be easy!

Column T is titled End Time and has 16:06 in cell T15
Column R is titled Start Time and has 17:45 in cell R15

I would like to subtract the end time from the start time, but when I enter =T15-R15 it automatically enters =[@[End-[@[Start Time]] and excel says "The syntax of this name isn't correct"

I would also like the formula to return a 0 if there is no end date entered.

"=IF(ISERROR([@[End" did give me hours and minutes, but if there was no end date, it just displayed the start time.

Any Help will be greatly appreciated

 

[bebo021999]

How about:
=[@[End]]-[@[Start Time]]
or
=[@[End Time]]-[@[Start Time]]
???

 

[Takes2ToTango]

Not sure if this si what you are looking for.

=IF(ISBLANK([@[End Time]]),0,[@[End Time]]-[@[Start Time]])

If End date is blank then a 0 will be given (I have formatted the cells as Time, however general will give a 0 also)

 

[Stef9910]

Not sure if this si what you are looking for.

=IF(ISBLANK([@[End Time]]),0,[@[End Time]]-[@[Start Time]])

View attachment 92125
View attachment 92126

If End date is blank then a 0 will be given (I have formatted the cells as Time, however general will give a 0 also)

This works a treat thank you, but i think i have it back to front the end time 16:06 and the start time is 17:45 and should result 22 hours and 21 minutes, when i swapped the end time and start time around for the minus part, it returns a lot of ##############

 

[Takes2ToTango]

This works a treat thank you, but i think i have it back to front the end time 16:06 and the start time is 17:45 and should result 22 hours and 21 minutes, when i swapped the end time and start time around for the minus part, it returns a lot of ##############

This would be due to the result being a negative number. So is the Result (End - Start = 22 hrs and 21 mins)?

 

[Stef9910]

This would be due to the result being a negative number. So is the Result (End - Start = 22 hrs and 21 mins)?

Yes that is correct 22 hours and 21 minutes

 

[Sufiyan97]

Try

Book2
	A	B	C	D
1
2	17:45	16:06	22:21
3
4
Sheet2

Cell Formulas
Range	Formula
C2	C2	=IF(B2="",0,IF(B2-A2<0,B2+1-A2,B2-A2))

 

[Stef9910]

2	=IF(B2="",0,IF(B2-A2<0,B2+1-A2,B2-A2)

That's amazing, worked brilliantly, thank you

 

[Sufiyan97]

You're welcome!
Glad we could help.