Formatting


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.


Useful Formulas - Dates

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



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