Time Rounding Up Question Access VBA

Posted on 2016-08-26
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.
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1

Author Comment

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.
LVL 50

Accepted Solution

Gustav Brock earned 500 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

LVL 26

Expert Comment

ID: 41772496
I am trying to get away without a function.
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!
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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

Expert Comment

ID: 41772534
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.
LVL 50

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.


Author Closing Comment

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.

Author Comment

ID: 41772693
Nick thank you too :-)

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 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