Convert Date In Access – Serial Number, Text Date, First of Month, End of Month/last day of month, Date to Text. It is fairly common when compiling date data that we need to convert it, whether for use in other apps (like Excel), outputting in a nicely readable format (Word, PDF, etc.) or to enable joins to other tables, possibly even in other systems.
MS Access will not automatically convert dates into different types from a menu, so I’ve created this handy post to quickly and easily assist you with doing so quickly.
INSTRUCTIONS
To begin, you will create a new field in your query, and then continue with the conversion below that applies in your scenario.
Helpful hint: Substitute [yourdate] with the name of the field that you are converting. In the images, the date in my table is called Journal_Date, so this is the one I am converting in each scenario. You should use the actual field name in your table.
To convert a date in the date/time format to the serial number (7/1/2019 to 43647), use:
Cdbl ([yourdate])
To convert a date in the serial number format to date/time format (43647 to 7/1/2019), use:
Cdate ([yourdate])
To convert a text or string date to the date/time format, try:
DateValue ([yourdate])
To convert a date to the first day of the month:
DateSerial (year([yourdate]),month([yourdate]),1)
To convert a date to the last day of the month/end of month:
DateSerial (year([yourdate]),month([yourdate])+1,1)-1
You can also convert a date/timeformat to a string – here is one example:
Format([yourdate],”mmmmyyyy”)
This would render 7/1/19 in your date field as July2019.
More information on the Format function and syntax can be found on the Microsoft Office Support site if you are looking to output the text differently.
Conclusion
Please feel free to contact me or comment if there are other tutorials/how-to articles you would like to see. I’d love to help you out.
If I helped you, please consider leaving a tip via PayPal to support the blog! Thanks!!
Subscribe for more great content!