Change Date to Current Month and Year

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Change Date to Current Month and Year

Post by Leesha »

Hi,
I have a user who has a table with over 54,000 rows of data with the day of week / store, but the month and year is not included. They need to update the date to be in short date format. So, if [COVIDDayNumber] = 1 then [COVIDSalesDate] would equal 5/1/2020, and so on. What would this formula look like in an update query?

Thanks
Leesha

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Change Date to Current Month and Year

Post by Wendell »

Maybe I'm dense this evening, but if you don't have the month or year, how are you going to get those to create a short date field, or did their data start on May 1 of 2020?
Wendell
You can't see the view if you don't climb the mountain!

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change Date to Current Month and Year

Post by HansV »

Leesha, you mention "day of week". Did you really mean that, or should it be "day of the month"?
Best wishes,
Hans

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: Change Date to Current Month and Year

Post by snb »

Maybe:

Code: Select all

Sub M_snb()
   [A1:A30000] = [index(if(A1:A30000="","",date(2020,5,A1:A30000)),)]
   [A30001:A60000] = [index(if(A300001:A60000="","",date(2020,5,A30001:A60000)),)]
End Sub

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change Date to Current Month and Year

Post by HansV »

@snb: in Access?
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Change Date to Current Month and Year

Post by Leesha »

Hi Hans and Wendell!
Sorry for the confusion, yes I meant day of the month. They have each day of the month (1, 2, 3, 4) but they need it to show as 5/1/2020, 5/2/2020, etc.
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change Date to Current Month and Year

Post by HansV »

For the month of May in 2020, the expression for COVIDSalesDate would be

DateSerial(2020, 5, [COVIDDayNumber])

If you want it to be for the current month (so as I write for June, 2020), it would be

DateSerial(Year(Date()), Month(Date()), [COVIDDayNumber])
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Change Date to Current Month and Year

Post by Leesha »

Thank you!!!