Formatting Dates

Formatting Dates in Excel

It is recommended you use date formats that begin with an asterisk (*) in the Format Cells dialog box.
Any date formatted in this way will display correctly in a different region.

when the date is 26/02/2009 
 
spastedate = Format(DateAdd("d", 3, Date()), "dd/mm/yyyy") 
spastedate = "03/01/2009" 

spastedate = FormatDateTime(DateAdd("d", 2, Date()), VbDateTimeFormat.vbShortDate) 
spastedate = "03/01/2009" 
 
Range("A1").Value = spastedate 

Depending on how the cell is formatted - can depend on how the results are displayed.
If the cell is formatted as a date then you can get "03/01/2009"
If the cell is formatted as text then you get "01/03/2009"

This will display the correct date value

Range("A1").Value = dtpastedate 

If you do have to have strings of dates - then make sure they are converted to dates before you paste them on to the sheet
To get todays date in a readable format you can use the Format() function, eg Format("Now(), "dd MMM YY") = "01 Jul 03".
You can use a string variable to store your dates but you obviously cannot perform any date or time calculations on it.

Universal Date Format

yyyy-mm-dd is a universal date string and will work across different region settings.
So when you use CDate("yyyy-mm-dd") it will work without issue and translate string to date value.

Useful Formulas - Dates

Date_PreviousMonthLastDay
=DateSerial(Year(Date), Month(Date), 1) - 1