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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top