Date references

Rakeforms

New Member
Joined
Aug 4, 2020
Messages
5
Office Version
  1. 2011
Platform
  1. MacOS
Hi, hoping for some help with a problem.
I am trying to build a cashing up sheet for use at my work, based on our new tills, which we can download daily reports from.
The idea being if I manually copy and paste the report into the top of the cash up sheet, hide it (with a group +/- button for convenience) then all the info will fill itself into the user friendly part of the sheet, we'll just have to count the money and its done.

The next step, would be a macro that does all that for me, which is what I'm working on.

The till system downloads the report sheet with a name such as "daily-cash-up-report-2021-09-04-2021-09-04" I can edit the "daily-cash-up-report" bit, but I can't edit the exports having the date range in their names.

So, no problem, I just have a cell in the sheet with today's date in it for reference.

So, in cell C60 I have =TODAY() and in cell D60 I have tried to enter a formula that will generate the name of the file which is going to be exported from the till:

="daily-cash-up-report-"&TEXT(RIGHT(C60,4),"YYYY")&"-"&TEXT(MID(C60,4,2),"MM")&"-"&TEXT(LEFT(C60,2),"DD")

I would therefore expect a return of: daily-cash-up-report-2021-09-04 (need to do date twice, but I'd like to get it right before complicating the sheet.
Instead it is returning: daily-cash-up-report-1912-02-13

What am I doing wrong?

Thanks!!!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
You can do it in the code:

VBA Code:
"daily-cash-up-report-" & Format(Date, "YYYY-MM-DD-YYYY-MM-DD")

or use a cell

VBA Code:
"daily-cash-up-report-" & Format(Sheets("Sheet1").Range("A1"), "YYYY-MM-DD-YYYY-MM-DD")
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,546
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
While I would
So, in cell C60 I have =TODAY() and in cell D60 I have tried to enter a formula that will generate the name of the file which is going to be exported from the till:

="daily-cash-up-report-"&TEXT(RIGHT(C60,4),"YYYY")&"-"&TEXT(MID(C60,4,2),"MM")&"-"&TEXT(LEFT(C60,2),"DD")

I would therefore expect a return of: daily-cash-up-report-2021-09-04 (need to do date twice, but I'd like to get it right before complicating the sheet.
Instead it is returning: daily-cash-up-report-1912-02-13

What am I doing wrong?
The TEXT function operates directly on the date... you do not (cannot) parse it like text. Your formula should be...

="daily-cash-up-report-"&TEXT(TODAY(),"yyyy-mm-dd")

However, since you are going to write a macro, I would use one of steve the fish's VBA code suggestions instead.

Just as a point of interest, had you decided to incorporate the date using mm-dd-yyyy as the format, VBA has a direct way to return the current date in that format.

="daily-cash-up-report-"&Date$

where the $ sign is required. Since you wanted yyyy-mm-dd as the format, I would suggest steve the fish's first suggested code snippet.
 

Forum statistics

Threads
1,147,571
Messages
5,741,895
Members
423,693
Latest member
Excelquestion35

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top