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


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext