By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

Returning a date based on date/time criteria

P: 5
The start of the business day is 0700. However actual times are recorded. I would like to have a field that can round to a date based on actual date/time that is entered.

I.E. If the date/time falls between 6/1 0701 and 6/2 0700 the field would return 6/2. So if the actual date/time is 6/1 03:15 the date would be 6/1, if the actual date/time was 6/1 14:25 it would return 6/2.
Jun 4 '12 #1

✓ answered by NeoPa

Take the DateValue() of the original value plus #16:59:59# (which is equivalent to a day minus #7:00:00# but with a second the other way to ensure 0700 itself is not rolled over).

Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,366
Use the IIf() function to check the time and if it's after 0700 and before 1200, add a day.
Jun 5 '12 #2

NeoPa
Expert Mod 15k+
P: 31,491
Take the DateValue() of the original value plus #16:59:59# (which is equivalent to a day minus #7:00:00# but with a second the other way to ensure 0700 itself is not rolled over).
Jun 5 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.