Excel Question

[importdistributors]

Ok, at work I am required to save excel files as "mfg name"."client name". "Client quote number"."Todays date in format without dashes or slashes ex 012609."internal quote number". "initials"


It would end up looking like Symantec.CityofChicago.46577854.012609.4658485.HT.xls

Ok This process is to tidious and I am using an excel spreadsheet anyways, so I thought I would make a formula that would gather each bit and piece from different cells and combine them. Here is what I came up with:

="QUOTE!B7&"."&QUOTE!B12&"."&data!F1&"."&data!G15&"."&data!B1&".HT"

Now the problem occurs with the date, the date when removed from the date format, ends up as a number like 6954785 instead of 012609. How can I fix this problem?

Thanks

 

[mayest]

You need to use the Text() function on the date. Dates are stored as the number of days since Jan 1, 1900 (on the PC, 1904 on the Mac). So, you are getting that instead of the date string that you want. You will also need to use the Year, Month, and Day functions within the Text function. So, assuming that the date is in A1, this should (almost) do the job:

=TEXT(DAY(A1)&MONTH(A1)&YEAR(A1),"#")

If you need the leading 0 for the day and month, then you will need to break that into three separate functions and set the format string appropriately.

 

[Ken g6]

Better yet, just do:

=TEXT(A1,"mmddyy")

 

[KLin]

a VBA macro would be perfect for this. It can grab all of the values, concatenate them, and even save a copy of the workbook with the new name. VBA is nice.

 

[importdistributors]

Thank you

 

[Evadman]

Originally posted by: KLin
a VBA macro would be perfect for this. It can grab all of the values, concatenate them, and even save a copy of the workbook with the new name. VBA is nice.

Quick version that will do "right now" in YYYYMMDD_HHMMSS format. Probably better ways of doing it with formatdate() or such, but I am lazy

datepart("YYYY",date()) & right("0" & datepart("M",date()),2) & right("0" & datepart("D",date()),2) & "_" & right("0" & datepart("H",now()),2) & right("0" & datepart("N",now()),2) & right("0" & datepart("S",now()),2)