You need to sign in to do that

Don't have an account?

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

Apoorv Saxena 4Hi 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 4Hi 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

Mohan Raj 33@Apoorv Oops! ya It's my mistake Thank You for the reply apoorv.