+ Start a Discussion
Mohan Raj 33Mohan Raj 33 

Why this text to number conversion providing this error"#ERROR!"

I have Try to get the text value to the Number (because the text has the charector of number) for my requirement  in formula fieldnbut it's provide the result instant of it's provide the "#ERROR" in the result page I don't know why If any body know please tell the solution to rectify this thing and my code is followingly,
VALUE(MID(RIGHT((TEXT(Calculating_Date__c - 0.2916)),9),0,5))
I want to do here to take a time portion of the Calculating Date field value if it is 10/14/2016 11:35 means  as like 11:35  or 23:35 is I want to in my output here but it's not working so that's my problem .For answer'sthanks in advance.Mohan(here I subtracting the value is My org is using (GMT - 7) so that's why).
 
Best Answer chosen by Mohan Raj 33
Apoorv Saxena 4Apoorv Saxena 4
Hi Mohan,

The error is because, you are expecting an output like 11:35, but your Formula return type is Number, and as you know ':' colon is not a number hence it throws an error.

Further, here's what you can do, If you want the output for  10/14/2016 11:35 to be like 11:35(including colon), then change your formula return type to Text and use the following formula :

MID(TEXT ( Calculating_Date__c - 0.2916),12,5)

If you want the formula field to be Number type , then you will have to let go of ':' colon here and the output would be like 1135 with the following formula:

VALUE (MID(TEXT ( Calculating_Date__c - 0.2916),12,2) + MID(TEXT ( Calculating_Date__c - 0.2916),15,2))

Hope this helps!

Please let me know how this works for you, mark this as Solved if this helps you so that others can view it as a proper solution.

Thanks,
​Apoorv

All Answers

Apoorv Saxena 4Apoorv Saxena 4
Hi Mohan,

The error is because, you are expecting an output like 11:35, but your Formula return type is Number, and as you know ':' colon is not a number hence it throws an error.

Further, here's what you can do, If you want the output for  10/14/2016 11:35 to be like 11:35(including colon), then change your formula return type to Text and use the following formula :

MID(TEXT ( Calculating_Date__c - 0.2916),12,5)

If you want the formula field to be Number type , then you will have to let go of ':' colon here and the output would be like 1135 with the following formula:

VALUE (MID(TEXT ( Calculating_Date__c - 0.2916),12,2) + MID(TEXT ( Calculating_Date__c - 0.2916),15,2))

Hope this helps!

Please let me know how this works for you, mark this as Solved if this helps you so that others can view it as a proper solution.

Thanks,
​Apoorv
This was selected as the best answer
Mohan Raj 33Mohan Raj 33
@Apoorv Oops! ya It's my mistake Thank You for the reply apoorv.