getting ###### during subtracting time.

[needXL]

Hello All,

I am facing a issue i just want to subtract time within end time to start time , unfortunately m getting ######### where the both "End time" and "Start time" is blank how i can fix it.

Thanks in advance.

 

[Khalid NGO]

Hi (Jitendra) ?

Perhaps your end time is greater then start time, and the cell is formatted as time or date.

Not sure about blank cells ####, have you tried format those cells to General?

What about posting a sample?

Regards,

 

[needXL]

sorry for wrong clarification
see the attached file i just want to ignore blank cells , which one giving me 23:30 just want here only blank cell.

 

[Khalid NGO]

Hi,

Make an if statement like:
=IF(A2&B2="","",MOD(B2-A2-$C$1,1))

Regards,

 

[needXL]

Hi,

Make an if statement like:
=IF(A2&B2="","",MOD(B2-A2-$C$1,1))

Regards,

dear sir,

awesome and thanks, but if possible could u please tell me what is the meaning of "","" in above formula.

 

[Khalid NGO]

dear sir,
awesome and thanks,

You are welcome dear.

what is the meaning of "","" in above formula.

"" (null string) means blank cell.
First "" is a part of logical test, and 2nd "" is placed as a Value if TRUE.

If both cells A2 and B2 are blank, then formula result will be blank, otherwise MOD(B2-A2-$C$1,1)

Hope it helped.

Regards,

 

[needXL]

You are welcome dear.



"" (null string) means blank cell.
First "" is a part of logical test, and 2nd "" is placed as a Value if TRUE.

If both cells A2 and B2 are blank, then formula result will be blank, otherwise MOD(B2-A2-$C$1,1)

Hope it helped.

Regards,

again thank u a lot sir,

for briefing me now i understand the logic.....you are a ""*"".

 

[Deepak]

again thank u a lot sir,

for briefing me now i understand the logic.....you are a ""*"".

Just for fun @Khalid NGO !!

After reading about null string, he marked you as star but covered by double null type , so after calculation it, it would be

either null*null*star*null*null = null

or

""*"" =

 

[Khalid NGO]

Just for fun @Khalid NGO !!

After reading about null string, he marked you as star but covered by double null type , so after calculation it, it would be

either null*null*star*null*null = null

or

""*"" =

Haha nice one Sir,
Its up to him, I will manage it with SUBSTITUTE

Regards,

 

[Deepak]

Haha nice one Sir,
Its up to him, I will manage it with SUBSTITUTE

Regards,

Good One! Practical use of SUBSTITUTE in real life.

 

[Marc L]

A sample of SUBSTITUTE in real life :

Wife to her husband at sunday morning :
- Honey, before to do a ride in forest this afternoon,
the car needs to be washed …

After a while, the husband to his 12 years old son :
- My boy, you must know children have some duties over their parents.
Before to attach your bike to the car - for the ride in forest this afternoon -
your mom wants the car washed before moving, so you'll pleased her !
​