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

convert date stamp to previous date

P: 56
7/19/2007 12:46:30 am. is in a field called CmplteDte. It is a date stamp of when the user completed the item. I need the time for a report but for a form that the supervisors use to QA the previous days work I format the date to 7/19/2007 using a query Format([CmplteDate],"mm/dd/yy") so that they see everything for that date and not just that time.

My questions is that when work is stamped after 12 midnight it date stamps to the next day, How do I convert these dates back to the previous date so that the date says 7/18/2007. The items are still the previous days work even though the user didn't complete the item until the next day. It would be from the time frame of 12:00 am to 4:00 am that needs to be converted back to the previous day.

Here is an example

7/19/2007 12:46:30 AM i want to formatt to 7/18/2007.
7/19/2007 1:18:52 AM I want to formatt to 7/18/2007
7/18/2007 8:59:49 AM formatted to 7/18/2007
7/18/2007 8:25:49 PM formatted to 7/18/2007
7/18/2007 8:15:55 PM formatted to 7/18/2007
7/18/2007 7:58:01 PM formatted to 7/18/2007
7/18/2007 7:42:10 PM formatted to 7/18/2007
Jul 19 '07 #1
Share this Question
Share on Google+
2 Replies

Expert 2.5K+
P: 3,072
Try to use as a field in your query:

Format([AanvraagDatum]-0,2000001;"yyyy-mm-dd") ' European setting with comma and semicolon as separator character

Format([AanvraagDatum]-0.2000001,"yyyy-mm-dd") ' US setting with dot as decial separator and comma as listseparator

As the time is reflected as a decimal fraction of the datenumber, you can subtract 0,2 to have a 4 hrs correction (24hrs=1day so 6 hrs = 0,2 day) I've added a very small fraction to include 4:00, but you can modify that when exactly 4:00 is excluded into 0,2

Aug 1 '07 #2

P: 18
I would just shift the recorded time by four hours:

Select *, DateAdd("h", -4, CmplteDte) As WorkDte From tblYourTable

To remove the time portion, apply Int:

Select *, Int(DateAdd("h", -4, CmplteDte)) As WorkDte From tblYourTable


PS: Nico, is that you from EE?
Aug 2 '07 #3

Post your reply

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