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.
