If time value is within 30 minutes Then....

[denvernuggets15]

There is time value in cell I30.
Current time Now() is in cell M17.
If the time value in I30 is within 30 minutes of the current time, Then msgbox "Hurry Up!"

for example, I30 is 3:00PM
M17 is 2:30PM
Then it should msgbox


My main problem is writing the within 30 minutes part. Anyone?

Thanks in advance!

[aussieboykie]

hrs = hour(now()-range("M17"))
mins = minute(now()-range("M17"))
if hrs > 0 or mins > 29 then msgbox "Hurry up!"

Regards, AB

[JapanDave]

You will have to start this manually, but it will send a warning message every 60 seconds.

You will have to state if there are more than one cell that you want to see if it is in 30min. I am guessing that you want a whole range to be tested and evaluate if a certain cell is within 30 min correct?

Please Login or Register  to view this content.

[denvernuggets15]

Thanks japandave and aussieboykie! There is only one cell that I need to test for ,I30. That cell can contain any time value the user decides to put in. If the value is within 30 min of the current time, then the macro will msgbox.

[JapanDave]

How do you plan on firing the macro? Just entering the time will not work.

[denvernuggets15]

I was thinking of letting it be a worksheet function, it will continuously check the times.

[JapanDave]

Ok, looks like you have it under control. If you need any help let me know.

[denvernuggets15]

auusieboykie, I ran into a problem running your code.
It says "runtime error 13 type mismatch" on the "hrs = hour(now()-range("M17"))" part
It does the same thing for the minute part too.

[JapanDave]

This is obviously aussieboykie's code, I just filled in the blanks, but if you are on excel 2010 the below code works in a standard module.

Please Login or Register  to view this content.

[denvernuggets15]

Thanks, the macro works, but it shows the msgbox even if the time value isn't within 30 minutes for some reason.

[JapanDave]

Is there a reason you are not using the code I provided for you? I could go through Aussies code and fix that , but set up correctly my code will automatically start and on top of that give you 5min reminders that you have to hurry up. You could make that 10 mins reminders or even 1 min reminders. Just a thought before I look at Aussies code and see where the problem lies.

[aussieboykie]

I wan't paying close enough attention! My code pops up a MsgBox if the time difference is 30 mins or more. A simple change fixes that...

Please Login or Register  to view this content.

Edited to repair typo [hrs < 1 replaced hrs < 0]

[denvernuggets15]

OK thanks for your help JapanDave, but I'm not looking for regular reminders. I meant if the time typed in the cell is within 30 minutes of Now(), then it will msgbox "hurry up"

[JapanDave]

Ok, I think you are misunderstanding me.

Try this next workbook. If you enter a time that is within 30mins in cell "M17" the macro with start, anything more than 30 mins and nothing with happen. What will happen from there is , until the 30 mins is up it will tell you to hurry and give you how many minutes that are left to complete. So if you are doing something else it will serve as a reminder unto that time is reached. Once the time is up the reminder stops until you enter a new time value.

Note: You can make the time between reminders anything you want, 5 mins. 10mins. They just have to be dividable of 30.

Anyway, give the below file a try. Change the time in cell "M17" to within 30 mins of now. eg if it is 12:00 enter 12:29 as see what happens.


@aussieboykie, I don't think that will work either.

[denvernuggets15]

Thank you, but for some reason it doesn't seem to work. I tried to do as you said but nothing happens.

[denvernuggets15]

ok this is a seperate workbook i made that compares numbers. If the one number is within 30 of the other, then "warning" will show.
If the numbers aren't within 30, then that cell will remain blank. you can change the numbers. Check it out and you'll see.

Now my problem is: how to do that with time. I'm guessing it's some formatting stuff?

Thanks for all of you guy's help!

[JapanDave]

I just downloaded the file, it is working fine. I am pretty sure I know what you want and the file will give what you are after, you just need to get it working for you first.

Just a few things.
1.When you open the file , is the cell "I30" showing today's date and the time on your PC?
2. Are you just changing the minutes and hours, leaving the date in place? Or are you just entering the time deleting the date in front of the time? (If you are just entering time and adjustment to the macro is needed, but for purposes of just checking if the macro is what you are after, just change the hours and minutes only in cell "M17" for the time being and leave the date in front of the time. If the date has changed in the cell "I30" then change the date to match it)
3. Are you entering a time which is within 30min in the future?

The cell M17 should look something like this.

Please Login or Register  to view this content.

[denvernuggets15]

To answer your questions:

1. cell "I30" shows just the time, not the date
2. I'm just entering the time, the date doesn't matter
3. Yes, the time value is within 30 minutes of the current time in the future, for example Now() = 16:30, Cell "I30" = 17:00

[JapanDave]

And there in lies the problem. I had the cells back to front. So if you could try again and change the time only in cell "I30". Please leave the date there for the time being. Make sure it is today's date and enter the time leaving the formatting how it is then press enter.

And need to understand when you are working with time the date "Does" matter.