Date Formating and building character strings

[C Brandt]

I have data files with the names based on dates ordered such that they
naturally appear in order:
Acct 2007-07-23.txt, etc.
In my spreadsheet, i search for complete data and when I find that I am
missing data for a specific date, I would like to enter that date, and have
a procedure work out the filename and load the specfic file.
The yyyy-mm-dd format used to ensure that the files fall in the order of
generation is a little unusual and users don't always catch this and enter
the date incorrectly.
I would simply like to ask for the date needed, and generate the file name
behind the user interface.

I know this doesn't work:
Date needed : 7/23/2007
DN=7/23/2007
Filename = "Acct" & year(DN) & "-" & Month(DN) & "_" & Day(DN) & ".txt"

Any suggestions would be appreciated.
Craig

 

[Ron Rosenfeld]

I have data files with the names based on dates ordered such that they
naturally appear in order:
Acct 2007-07-23.txt, etc.
In my spreadsheet, i search for complete data and when I find that I am
missing data for a specific date, I would like to enter that date, and have
a procedure work out the filename and load the specfic file.
The yyyy-mm-dd format used to ensure that the files fall in the order of
generation is a little unusual and users don't always catch this and enter
the date incorrectly.
I would simply like to ask for the date needed, and generate the file name
behind the user interface.

I know this doesn't work:
Date needed : 7/23/2007
DN=7/23/2007
Filename = "Acct" & year(DN) & "-" & Month(DN) & "_" & Day(DN) & ".txt"

Any suggestions would be appreciated.
Craig

="Acct "&TEXT(DN,"yyyy-dd-mm") & ".txt"
--ron

 

[C Brandt]

Ron:

Thanks for the speedy reply. Unfortunately it chokes with TEXT highlighted
and gives me a popup with "Compile Error, SUB or Function not defined.
(Code Follows)

Any clues?

Thanks,
Craig


Sub test()
' Cells(3,2) has the date (7/23/07)
DN = Cells(3, 2)
Filename = "Acct " & Text(DN, "yyyy-dd-mm") & ".txt"
End Sub

 

[Dave Peterson]

Ron gave you a formula that will work in a cell in a worksheet (=text()).

In code, you'd use:
Filename = "Acct " & Format(DN, "yyyy-dd-mm") & ".txt"

Ron:

Thanks for the speedy reply. Unfortunately it chokes with TEXT highlighted
and gives me a popup with "Compile Error, SUB or Function not defined.
(Code Follows)

Any clues?

Thanks,
Craig

Sub test()
' Cells(3,2) has the date (7/23/07)
DN = Cells(3, 2)
Filename = "Acct " & Text(DN, "yyyy-dd-mm") & ".txt"
End Sub

 

[Ron Rosenfeld]

Ron:

Thanks for the speedy reply. Unfortunately it chokes with TEXT highlighted
and gives me a popup with "Compile Error, SUB or Function not defined.
(Code Follows)

Any clues?

Thanks,
Craig

The formula I gave you is for a worksheet cell.

In VBA, in place of the TEXT function, you can use the FORMAT function.


Sub foo()
Const DN As Date = #7/23/2006#
Debug.Print "Acct " & Format(DN, "yyyy-mm-dd") & ".txt"
End Sub

--> Acct 2006-07-23.txt
--ron

 

[C Brandt]

Thanks

Craig

The formula I gave you is for a worksheet cell.

In VBA, in place of the TEXT function, you can use the FORMAT function.


Sub foo()
Const DN As Date = #7/23/2006#
Debug.Print "Acct " & Format(DN, "yyyy-mm-dd") & ".txt"
End Sub

--> Acct 2006-07-23.txt
--ron