**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.*