Hi,
I have a need to fill in a spreadsheet as time advances during the day every 15 minutes. So in column A, I simply created those time values (with a formatting of "time 13:30"), namely:
8:00
8:15
8:30
8:45
In an adjacent column, I wish to take action if I'm between those time values. My formula isn't working, it is
=IF(AND(NOW()>=A1,NOW()<A2),"Do 1","Do 2")
Can you tell me how to fix this?
Thanks
If you want just the time, add MOD()
e.g.
=IF(AND(MOD(NOW(),1)>=A1,MOD(NOW(),1)<A2),"Do 1","Do 2")
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Welcome to the Forum!
NBVC has answered your question but I thought I would add some explanation around it.
NOW() returns date + time. So for example, as I am typing this it returns
9/30/2011 14:43
and the underlying numeric value is 40816.613232. When you put in a time, you only get the decimal portion. For example, 08:00 has a value of 0.3333 (repeating) because it represents the fraction of a 24-hour day.
So to compare NOW() to a time value you have to lop off the interest portion from NOW().
As NBVC so concisely has done.
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
Wonderful, thanks NBVC, it works great.
Thanks too 6StringJazzer, for explaining the underlying time format and why it works.
apologies for the conciseness
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
You can use =NOW()-TODAY() or =NOW()-INT(NOW()) for example
"Relax. What is mind? No matter. What is matter? Never mind!"
Those should both also work.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks