Computer_Support_Norwich
asked on
Time Rounding Up Question Access VBA
The following code successfully rounds DOWN my time NOW() to the nearest 5 minutes, but I am struggling to edit it to round UP.
How can I do this please? I am trying to get away without a function.
Me.txtStart.Value = CDate((1 * (Int(Now() / (1 * (1 / 288)))) * (1 / 288)))
How can I do this please? I am trying to get away without a function.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
gustav's is always good, as he is the Master of all things date, time and rounding :-)
<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.
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.
I've remarked on that and suggested an Article should be writtenI know and remember just too well, and I'm about it - turns out as a major task.
Alas! It hasn't been forthcoming.
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
ASKER
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.
Why do I want to get away without a function? It's just easier for me in this situation.
ASKER
Nick thank you too :-)
ASKER
Open in new window
Seems it might be about right.