Solved

Time Rounding Up Question Access VBA

Posted on 2016-08-26
8
47 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 49

Accepted Solution

by:
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

/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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 49

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

860 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