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
Change Date to Current Month and Year
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: Change Date to Current Month and Year
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!
You can't see the view if you don't climb the mountain!
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change Date to Current Month and Year
Leesha, you mention "day of week". Did you really mean that, or should it be "day of the month"?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 548
- Joined: 14 Nov 2012, 16:06
Re: Change Date to Current Month and Year
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
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Change Date to Current Month and Year
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
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
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change Date to Current Month and Year
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])
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
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Change Date to Current Month and Year
Thank you!!!