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.
