Passing date as text in different format in Excel

Hello,

I need to take todays date and add it to the end of a function as itself in a separate format, then I need to take the date from a month ago, from 6 months ago, and from 12 months ago and do the same thing. This isn't very clear and is best illustrated with an example.

I have a date:

=TODAY()      -> 3/23/2011

I need this date to become a text string in the form of:

CellA1: 20110323

which gets added to some text in a function:

="TEXT="&A1

Which should result in my final text becoming "TEXT=20110323"

I then need to do this for a month ago and 6 months ago and a year ago.

Right now I create the date =TODAY() in the format yyyymmdd, but when I add this to the end of my string, it becomes 40625, the converted Microsoft date.

Thanks!
keats88Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Any date can be formatted as you desire with:

   TEXT(Date, "YYYYMMDD")

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
To find dates so many months in the past use the EDATE function:

EDATE(StartDate, Months) - Returns the date time serial number of the date that is the indicated number of months before or after the parameter StartDate. Requires the Analysis Toolpak.

Kevin
0
 
keats88Author Commented:
Oh man, that worked. I have no idea why I thought it wouldn't and didn't bother trying it. Thanks
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
nutschCommented:
And to get to the 3, 6 12 months ago, your best bet is to install the analysis toolpak add-in and use the edate formula. So with Kevin's suggestion

=text(edate(today(),-6),"YYYYMMDD")

will give you the date 6 months ago, as text in the right format

Thomas
0
 
zorvek (Kevin Jones)ConsultantCommented:
So, to do what you want with a date six months in the past:

="TEXT="&TEXT(EDATE(NOW(),-6),"YYYYMMDD")

Kevin
0
 
keats88Author Commented:
Thanks guys, this other stuff is helpful too, I wish I hadn't already picked the solution so I could give you guys some points. Sorry :-(
0
 
nutschCommented:
Most of the you guys are the one you accepted the solution of, so I guess you're fine.

T
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.