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

Date Formats

(/)	Date seperator. This seperates years, months and days when date values are formatted
ddddd	Displays the date as a complete date (day, month & year) formatted to a long date setting. The default for Windows is "m/d/y"
dddddd	Displays a date serial number as a complete (day, month & year) formatted to a long date setting. The default for Windows is "mmmm dd, yyyy"
c	Displays the date as "ddddd" and displays the time as "ttttt", in that order
d	Displays the day as a number without a leading zero (1 - 31)
dd	Displays the day as a number with a leading zero (01 - 31)
ddd	Displays the day as an abbreviation (Sun - Sat)
dddd	Displays the day as a full name (Sunday - Saturday)
w	Displays the day of the week as a number (1 for Sunday - 7 for Saturday)
ww	Displays week of the year as a number (1 - 54)
m, M	Displays the day of the month as a number without a leading zero (1-12). If "m" immediately follows "h" or "hh" then the minute is displayed rather than the month
mm	Displays the month as a number with a leading zero (01 - 12)
mmm	Displays the month as an abbreviation (Jan - Dec)
mmmm	Displays the month as a full name (January - December)
q	Displays the quarter of the year as a number (1 - 4)
y	Displays the day of the year as a number (1 - 356)
yy	Displays the year as a 2-digit number (00 - 99)

Time Formats

(:)	Time seperator. This seperates hours, minutes and seconds when time values are formatted
ttttt	Displays the time as a complete time (second, minute & hour) formatted to a ???. The default for Windows is "h:mm:ss"
yyyy	Displays the year as a 4-digit number (100 - 1999)
h	Displays the hour as a number without a leasing zero (0 - 23)
hh	Displays the hour as a number with a leading zero (00 - 23)
n	Displays the minute as a number without a leading zero (0 - 59)
nn	Displays the minute as a number with a leading zero (00 - 59)
s	Displays the second as a number without a leading zero (0 - 59)
ss	Displays the second as a number with a leading zero (00 - 59)
AM/PM	Use the 12 Hour clock and display in uppercase
am/pm	Use the 12 Hour clock and display in lowercase
A/P	Use the 12 Hour clock and display in uppercase
a/p	Use the 12 Hour clock and display in lowercase

Time Zone

link - cpearson.com/excel/TimeZoneAndDaylightTime.aspx 
Private Type SYSTEMTIME 
    wYear As Integer 
    wMonth As Integer 
    wDayOfWeek As Integer 
    wDay As Integer 
    wHour As Integer 
    wMinute As Integer 
    wSecond As Integer 
    wMilliseconds As Integer 
End Type 
    
Private Type TIME_ZONE_INFORMATION 
    Bias As Long 
    StandardName(0 To 31) As Integer 
    StandardDate As SYSTEMTIME 
    StandardBias As Long 
    DaylightName(0 To 31) As Integer 
    DaylightDate As SYSTEMTIME 
    DaylightBias As Long 
End Type 
    
Private Enum TIME_ZONE 

    TIME_ZONE_ID_INVALID = 0        ' Cannot determine DST  
    TIME_ZONE_STANDARD = 1          ' Standard Time, not Daylight  
    TIME_ZONE_DAYLIGHT = 2          ' Daylight Time, not Standard  

End Enum 

Private Declare Function GetTimeZoneInformation Lib "kernel32" _ 
    (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long 

Private Declare Sub GetSystemTime Lib "kernel32" _ 
    (lpSystemTime As SYSTEMTIME) 

Function IntArrayToString(V As Variant) As String 

    Dim N As Long 
    Dim S As String 
    For N = LBound(V) To UBound(V) 
        S = S & Chr(V(N)) 
    Next N 
    IntArrayToString = S 

End Function 

Sub Testing() 

    Dim TZI As TIME_ZONE_INFORMATION 
    Dim DST As TIME_ZONE 
    Dim StandardName As String 
    
    DST = GetTimeZoneInformation(TZI) 
    StandardName = IntArrayToString(TZI.StandardName) 
    Debug.Print StandardName 

End Sub