Date Formating and building character strings

  • Thread starter Thread starter C Brandt
  • Start date Start date
C

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
 
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
Click to expand...

="Acct "&TEXT(DN,"yyyy-dd-mm") & ".txt"
--ron
 
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 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"

C said:
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
Click to expand...
 
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
Click to expand...

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
 
Thanks

Craig


Ron Rosenfeld said:
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
Click to expand...
Click to expand...
 
Back
Top