Date Prompt in Excel

[steelcoyot]

Hey team I'm looking for a way to have a user be prompted for a date. I also want to have the date populate 4 other cells with dates that are of set by 7 days. So if you put in 210406 for the date it will populate the other cells with 140406, 070406 and so forth. I also need to have the dates in that format so that I can pull data from an external website. The date formula I have now is this:

=((IF(LEN(DAY(TODAY()-7))<2,"0"&DAY(TODAY()-7),DAY(TODAY()-7))))&"0"&MONTH(TODAY()-7)&RIGHT(YEAR(TODAY()),2)

This formula off sets today date by 7.

Any help is highly appreciated.

 

[macropod]

Hi steelcoyot,

To calculate 7 days from today, try:
=TODAY()+7
Much simpler.

As for prompting, what do you want to use to trigger the prompt (eg selecting a cell, changing worksheets, worksheet recalcs)?

Cheers

[MS MVP - Word]

 

[steelcoyot]

I can't have the date off set in that fashion do to the way excel handles data when passing it off to external sites. I want the date prompt when the spread sheet is open.

 

[macropod]

Hi steelcoyot,

In that case, try:
=TEXT(TODAY()+7,"dd-mm-yy")
Still much simpler. Change "dd-mm-yy" to whatever date format you prefer.

As for the prompt at open, at its simplest you could use something like:

Sub Auto_Open()
ThisWorkbook.Sheets("MySheet").Range("A1").Value = InputBox("Please Input a Date", "DatePrompt")
End Sub

but you might want to add some validation to it to ensure you get a valid date, and that it falls within a valid range.

Cheers

[MS MVP - Word]

 

[SkipVought]

"So if you put in 210406 for the date..."

You do not understand Excel Dates. 210406 is NOT the date that you expect. 210406 is REALLY 1/25/2476.

FYI: Why do Dates and Times seem to be so much trouble? faq68-5827

So is you must pass the date as a STRING to other applications, first, use REAL DATES to do the date calculations. THEN convert the result to a STRING via the TEXT function like...
[tt]
=TEXT(A1,"DDMMYY")
[/tt]
where A1 contains a REAL DATE.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}