Thread: Auto-populate with one day prior to "today"

Hello, all.

Is there a way to auto-populate a box with the day prior to the current date? I have a box where I want to say the "Current version supersedes versions before x date." (X being today)

I have a list of events, which will typically fit on one page, but I have the header, footer and two more cells set to repeat per printed page. And I would really like the x date statement to function properly across the top to where I need not edit the "x" date because I am sure I will get lost keeping up with which dates I print the document.

And I know how to get the current date and manipulate it with macros, but I am not an excel fanatic by any means!

Thanks for your help.

Hi and welcome to the forum.

What kind of "box" are we talking about? If it is a cell, just do

="Current version supersedes versions before "&text(today(),"dd-mmm-yyyy")&" date."

Or are you doing this in VBA?

mystring = "Current version supersedes versions before " & DATE & " date."

Or in a regular header (Page setup dialog, Header/Footer tab)

Current version supersedes versions before &[Date] date.

Take your pick.

cheers

teylyn:

Geez, thanks for making me feel like an idiot by not thinking of

Originally Posted by teylyn

Current version supersedes versions before &[Date] date.

Now that leads to another question though.

I have the current date down in the bottom right cell, and I used macros to get the date to print in a certain format as I am sure everyone has different settings on their machine's long-day/short-day format.

Here is what I have for the bottom right:

Code:

Sub Change_Format()
    ActiveSheet.PageSetup.RightFooter = Format(Now, "DD MMM YY")
End Sub

So, how would I be able to force the same formatting in CenterHeader cell as well?

Criminy...

Come to find out, the code supplied above worked great yesterday, but it is not working today. Meaning today's date is not shown; it shows yesterday's date instead. Any brilliant suggestions for that?

Hmm, I guess the Date field in the header is not dynamic. Word has better control in that respect. I feel that Excel headers and footers have always been just an afterthought hastily patched on to fulfill some basic requirements, but not the real thing.

I guess you will have to resort to code to set the header. Something like

Code:

ActiveSheet.PageSetup.CenterHeader = "Current version supersedes versions before " & Format(Now, "DD MMM YY") & " date."

Thanks, teylyn. I will try that first thing when I get back to work! http://reaganator.com/comments/super.gif

Teylyn:

Thank you much for all your help! I have it figured out now so the dates will load properly upon the file being opened, so it is all good now.

Ciao!