Dates

azizrasul

Well-known Member
Joined
Jul 7, 2003
Messages
1,290
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I have been doing a study in dates for some time as I have a DB that uses them a lot. I would appreciate it if someone can confirm my findings. My computer settings viz. Regional Options settings of the Control Panel are English(U.K.).

Date literals used in queries
1. Calculations of dates & interpretation of dates are determined by the computer settings.
2. Where dates are written in English(U.S.) format, they are changed on screen as per the computer settings e.g. #01/13/2004# to #13/01/2004#.

String dates used in queries
1. Calculations of dates & interpretation of dates are determined by the computer settings.
2. Where dates are written in English(U.S.) format, they are NOT changed on screen as per the computer settings but are calculated as per the computer settings none the less e.g. DateAdd("ww",1,"01/13/2003") = 20/01/2003.

Date literals used in VBA
Dates should and will be interpretated as English(U.S.) and calculated as such but will output the date as per the computer settings e.g. DateAdd("ww", 1, #1/11/2003#) = 18/01/2003 i.e. 18th of Jan

String dates used in VBA
1. Dates will be interpretated as per the computer settings and outputs as per the computer settings as well. For example DateAdd("ww", 1, "1/11/2003") = 08/11/2003 i.e. 8th of Nov
2. Dates that could NOT comply according to the computer settings will be interpretated as English(U.S.) but will still output the result as per the computer settings e.g. DateAdd("ww", 1, "1/13/2003") 20/01/2003 i.e. 20th of Jan

Conclusion
In queries use the computer settings to write dates. The results will also comply to the computer settings.
In VBA dates should be written as English(U.S.) and the resulting dates will follow thcomputer settings format. If the dates are required to be written according to the computer settings, then use the Format function to 'persuade' the dates in that way.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,061
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top