**Question description:**

*This user has given permission to use the problem statement for this blog.*

I am trying to see if a certain time in cell AU, example 3:26 PM, is before 6:59 am or after 7:00 pm. It if is then I need to add $10.00

Solved by A. B. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
11/06/2018 - 02:25

Hello

Excelchat Expert
11/06/2018 - 02:25

How are you today?

User
11/06/2018 - 02:25

ok and you

User
11/06/2018 - 02:25

my question is: I am trying to see if a certain time in cell AU, example 3:26 PM, is before 6:59 am or after 7:00 pm. It if is then I need to add $10.00

Excelchat Expert
11/06/2018 - 02:25

Great, thank you.

Excelchat Expert
11/06/2018 - 02:26

Ok.

Excelchat Expert
11/06/2018 - 02:26

Is it possible to share the excel file with me?

User
11/06/2018 - 02:26

what would be the best formula for that

User
11/06/2018 - 02:26

I actually can't

Excelchat Expert
11/06/2018 - 02:26

Ok.

Excelchat Expert
11/06/2018 - 02:26

Problem

Excelchat Expert
11/06/2018 - 02:26

No problem*

Excelchat Expert
11/06/2018 - 02:27

so condition 1 - Before 6.59 am

User
11/06/2018 - 02:27

yes

Excelchat Expert
11/06/2018 - 02:27

Condition 2 - After 7 Pm

User
11/06/2018 - 02:27

and second is if the time is after 7:00 pm

Excelchat Expert
11/06/2018 - 02:28

then you need to add $ 10 which cell?

User
11/06/2018 - 02:29

within the same cell as the answer. if it is not within those times then we can return 0

User
11/06/2018 - 02:29

so lets say column b has a list of times.

Excelchat Expert
11/06/2018 - 02:30

Can you see the preview here

User
11/06/2018 - 02:30

I need column C to run the condition and return the answer

User
11/06/2018 - 02:30

yes

Excelchat Expert
11/06/2018 - 02:30

so if the condition is met the result will be 110

User
11/06/2018 - 02:30

10.00

Excelchat Expert
11/06/2018 - 02:30

Ok

User
11/06/2018 - 02:31

if it is not then 0

Excelchat Expert
11/06/2018 - 02:31

Here is the formula

Excelchat Expert
11/06/2018 - 02:32

Please check in the preview

User
11/06/2018 - 02:33

awesome let me try it

Excelchat Expert
11/06/2018 - 02:34

This is the formula without any additional cells

Excelchat Expert
11/06/2018 - 02:35

=IF(B2>0.29,IF(B2<0.79,10,0))

User
11/06/2018 - 02:36

so do we use the .29 or the time

Excelchat Expert
11/06/2018 - 02:36

.29 is equal 6:59 am in time

Excelchat Expert
11/06/2018 - 02:36

excel always convert time to a number and do the calculation

Excelchat Expert
11/06/2018 - 02:36

so we can always put number as excel will read only numbers

User
11/06/2018 - 02:37

how did you convert the time to a number?

Excelchat Expert
11/06/2018 - 02:38

will you be converting it on excel or google spreadsheet?

User
11/06/2018 - 02:38

Excel

Excelchat Expert
11/06/2018 - 02:38

On Excel - Right Click on Cell

Excelchat Expert
11/06/2018 - 02:38

Formal Cell

Excelchat Expert
11/06/2018 - 02:39

Then select numbers

Excelchat Expert
11/06/2018 - 02:39

and click Ok

Excelchat Expert
11/06/2018 - 02:40

is it working at your end?

User
11/06/2018 - 02:41

oh ok so it will automatically change the time

Excelchat Expert
11/06/2018 - 02:41

Yes

User
11/06/2018 - 02:42

this is the formula I used

User
11/06/2018 - 02:42

=IF(AU2<0.29,IF(AU2>0.79,10,0))

User
11/06/2018 - 02:42

the answer is either False or 0

Excelchat Expert
11/06/2018 - 02:42

0

Excelchat Expert
11/06/2018 - 02:43

if you want we can make the answer False also

User
11/06/2018 - 02:43

I rather have it 0

User
11/06/2018 - 02:43

I think i did something wrong let me change the signs around

Excelchat Expert
11/06/2018 - 02:44

Yes the signs need to be reversed

User
11/06/2018 - 02:44

when I did it listed 10 but 3:26 PM is not after 7:00pm or before 6:59 am

Excelchat Expert
11/06/2018 - 02:45

=IF(AU2<0.29,IF(AU2>0.79,10,0))

Excelchat Expert
11/06/2018 - 02:47

is this working?

User
11/06/2018 - 02:48

It returns False

User
11/06/2018 - 02:48

shouldn't it return 0

Excelchat Expert
11/06/2018 - 02:49

Yes, it should

Excelchat Expert
11/06/2018 - 02:49

the format of AU2

User
11/06/2018 - 02:50

I think I have the format as time

User
11/06/2018 - 02:50

short time 3:26 pm etc

Excelchat Expert
11/06/2018 - 02:50

Ok.

User
11/06/2018 - 02:50

should it be

Excelchat Expert
11/06/2018 - 02:51

As you can see this is working fine on the preview.

User
11/06/2018 - 02:51

and the ones that are true show 0

Excelchat Expert
11/06/2018 - 02:52

Ok

Excelchat Expert
11/06/2018 - 02:52

Let try with another formula

User
11/06/2018 - 02:52

ok

Excelchat Expert
11/06/2018 - 02:52

me send you another formula

User
11/06/2018 - 02:52

oh even though I have it as short time it still shows 3:26:00 PM

Excelchat Expert
11/06/2018 - 02:53

=IF(OR(AU2<0.29,AU2>0.79),10,0)

Excelchat Expert
11/06/2018 - 02:53

TRY THIS

User
11/06/2018 - 02:53

yes perfect that worked!

Excelchat Expert
11/06/2018 - 02:54

Great!

User
11/06/2018 - 02:54

Thank you soooo much!

Excelchat Expert
11/06/2018 - 02:54

Regret about the first formula.

User
11/06/2018 - 02:54

I have the hardest time with working with time in Excel

Excelchat Expert
11/06/2018 - 02:54

There is some formatting issue which might be preventing it.

User
11/06/2018 - 02:54

I figured. there always seems to be

User
11/06/2018 - 02:55

Thank you so much for your help!

Excelchat Expert
11/06/2018 - 02:55

Great!

Excelchat Expert
11/06/2018 - 02:55

If you like my solution please do provide a good feedback.

Excelchat Expert
11/06/2018 - 02:55

Always happy to help

Excelchat Expert
11/06/2018 - 02:55

Have a great day ahead!

User
11/06/2018 - 02:55

will do!

User
11/06/2018 - 02:55

you as well!

Excelchat Expert
11/06/2018 - 02:56

If all is in order, can you please end the session so that I can assist others.

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