# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

How do I make this formula =Value(IFerror(TEXT(AM3,"HMM"),"0")) Return a value of 2400 if the value is not an error?
Solved by F. J. in 21 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 13/02/2018 - 08:51
Hello
User 13/02/2018 - 08:52
hey!
User 13/02/2018 - 08:52
How do I make this return 2400 instead of 0
User 13/02/2018 - 08:53
Without altering the formula HMM part
User 13/02/2018 - 08:53
Like If answer = 0 then 2400
Excelchat Expert 13/02/2018 - 08:54
The formula is return 0 if the text formula return error.
User 13/02/2018 - 08:55
If I remove the error
User 13/02/2018 - 08:55
ignore that
User 13/02/2018 - 08:55
It's 0 right now
Excelchat Expert 13/02/2018 - 08:55
Ok
User 13/02/2018 - 08:55
How do I make it return 2400
User 13/02/2018 - 08:55
if the value is 0
Excelchat Expert 13/02/2018 - 08:56
That is because the time is AM.
User 13/02/2018 - 08:57
I can't change A1
User 13/02/2018 - 08:57
A1 needs to stay as 12AM
User 13/02/2018 - 08:57
So the value in B2
User 13/02/2018 - 08:57
How do I do an if statement for IF 0 then 2400
Excelchat Expert 13/02/2018 - 08:58
I know but in the time format the 12:00AM is 0
Excelchat Expert 13/02/2018 - 08:58
Because it is the first hour of the day .
User 13/02/2018 - 08:58
Yes
Excelchat Expert 13/02/2018 - 08:59
You can make this using if statement when the result is 0
User 13/02/2018 - 08:59
But how can I make it return 2400
User 13/02/2018 - 08:59
With a formula
User 13/02/2018 - 08:59
?
User 13/02/2018 - 09:00
like putting that in the formula of B
User 13/02/2018 - 09:00
Perfect
User 13/02/2018 - 09:00
you did it I think
User 13/02/2018 - 09:00
yaaay
Excelchat Expert 13/02/2018 - 09:00
Yes
User 13/02/2018 - 09:00
That's all I needed lol
Excelchat Expert 13/02/2018 - 09:01
I used the formula if()
Excelchat Expert 13/02/2018 - 09:01
=if(Value(TEXT(A1,"HMM"))=0,2400,Value(TEXT(A1,"HMM")))
User 13/02/2018 - 09:01
but now the issue is
User 13/02/2018 - 09:01
what if it is 7am
User 13/02/2018 - 09:01
Oop
User 13/02/2018 - 09:01
still works
Excelchat Expert 13/02/2018 - 09:01
Yes
Excelchat Expert 13/02/2018 - 09:02
I put the if statement to work with your formula.
Excelchat Expert 13/02/2018 - 09:03
Just when your formula return 0 the result changes to 2400
Excelchat Expert 13/02/2018 - 09:04
Anything else>
User 13/02/2018 - 09:04
but
User 13/02/2018 - 09:04
what if it returns an error
User 13/02/2018 - 09:04
how do I make it stay 0
User 13/02/2018 - 09:05
for example
User 13/02/2018 - 09:05
if the value is #NA
User 13/02/2018 - 09:05
and I want it to return 0
User 13/02/2018 - 09:05
using like =IFERROR?
Excelchat Expert 13/02/2018 - 09:05
Ok
Excelchat Expert 13/02/2018 - 09:09
When error you want to return 0 or 2400]
User 13/02/2018 - 09:09
You can make it return 1 if 0 will break it
User 13/02/2018 - 09:09
0 if it's an error
User 13/02/2018 - 09:09
2400 if the answer is actually 0 and not because of an error
Excelchat Expert 13/02/2018 - 09:10
Check the formula>>>
User 13/02/2018 - 09:11
perfect
Excelchat Expert 13/02/2018 - 09:11
Is there anything else you want to know regarding this issue?
User 13/02/2018 - 09:12
nope ty tyt

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.