Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

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!
0
keats88
Asked:
keats88
  • 3
  • 2
  • 2
1 Solution
 
zorvek (Kevin Jones)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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now