[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Time Rounding Up Question Access VBA

Posted on 2016-08-26
8
Medium Priority
?
80 Views
Last Modified: 2016-08-27
The following code successfully rounds DOWN my time NOW() to the nearest 5 minutes, but I am struggling to edit it to round UP.

Me.txtStart.Value = CDate((1 * (Int(Now() / (1 * (1 / 288)))) * (1 / 288)))

Open in new window


How can I do this please? I am trying to get away without a function.
0
Comment
  • 3
  • 2
  • 2
  • +1
8 Comments
 

Author Comment

by:Computer_Support_Norwich
ID: 41772358
Hmmm, is this it?

Me.txtFinish.Value = (CDate(Round(DateAdd("n", 3, Now()) * 288, 0) / 288))

Open in new window


Seems it might be about right.
0
 
LVL 53

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 41772444
This will do:

Dim datTime As Date

datTime = Now

Me!txtFinish.Value = DateValue(datTime) + TimeSerial(Hour(datTime), -Int(-Minute(datTime) / 5) * 5, 0)

Open in new window

/gustav
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41772496
I am trying to get away without a function.
Why?
And /gustav's might be good too.
I didn't test it

Here's mine

Me.txtStart.Value = CDate(Int(Now) + (Int((CDbl(Now) - Int(Now)) * 24 * 12) + 1) / (24 * 12))
Here's the logic
We're going to coerce the date to a Double and remove the day part
CDbl(Now) - Int(Now)
That leaves us with the fraction of the day
There's 24 hours in a day and 12 five minute periods in an hour, so we'll change the fraction into 5 minute periods
CDbl(Now) - Int(Now) *24*12
Now we'll truncate that to get rid of fractional sub 5 minute amounts
Int(CDbl(Now) - Int(Now) *24*12)
Then add one to round up
Int(CDbl(Now) - Int(Now) *24*12) + 1
Convert it back to a fraction of a day
(Int(CDbl(Now) - Int(Now) *24*12) + 1)/(24*12)
Add the date whole number back on
Int(Now) + (Int(CDbl(Now) - Int(Now) *24*12) + 1)/(24*12)
And coerce it to a date
CDate(Int(Now) + (Int(CDbl(Now) - Int(Now) *24*12) + 1)/(24*12))

But a function would make for much more maintainable code!
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 75
ID: 41772513
gustav's is always good, as he is the Master of all things date, time and rounding :-)
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41772534
<grin>
I've remarked on that and suggested an Article should be written
Alas!  It hasn't been forthcoming.

But the ask was for 'not a function'
You can turn /gustav's into 'not a function' but it doesn't start that way.

It's been a while since I've played with Hijri dates.
Those were interesting questions.
Depending on many things, a day can be the last day of the month in one country and the first day of the month in another.
0
 
LVL 53

Expert Comment

by:Gustav Brock
ID: 41772675
I've remarked on that and suggested an Article should be written
Alas!  It hasn't been forthcoming.
I know and remember just too well, and I'm about it - turns out as a major task.

But the ask was for 'not a function'
That's why I didn't write a function ... just inserted two lines before the existing code line.

/gustav
0
 

Author Closing Comment

by:Computer_Support_Norwich
ID: 41772692
Thank you Gustav worked first time.

Why do I want to get away without a function? It's just easier for me in this situation.
0
 

Author Comment

by:Computer_Support_Norwich
ID: 41772693
Nick thank you too :-)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

611 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question