Convert Date in Access – Serial Number, Text String, First of Month, etc.

Convert Date In Access – Serial Number, Text Date, First of Month, End of Month/last day of month.  MS Access will not automatically convert dates into different types, so this post will assist you with doing so manually. You will create a new field in your query to accomplish this. 

Hint: Substitute [yourdate] with the name of the field that you are converting.

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:

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.

Please comment below or share the post if this information helped you! Thanks!