[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Time Rounding Up Question Access VBA

Posted on 2016-08-26
8
Medium Priority
?
76 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 52

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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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 52

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses
Course of the Month17 days, 18 hours left to enroll

831 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