Convert Serial Number to Date WITH TEXT

**HP RodNuclear** · 12-20-2010, 11:03 AM

Hello,
I know one can convert a serial number to a date by using formatting, but I am needing this to be done b/c the serial number is concatenated with some text (the word week i.e.
1/3/11 Week). However, I did try to format the cell but Excel keeps the date as a serial number.
The other half of my issue is I need to do this for a whole year of dates in a specified range and I was wondering if someone could give me an example of a macro I'd write up to basically change the bolded cells in the attached workbook to this format: m/d/yy along with the word 'week' concatenated to it and for these cells to refer to the first cell in the cells that precede it. Like I said, see the attached workbook to get a clear understanding of what I am trying to do. Thnx.

-HP RodNuclear

**antoka05** · 12-20-2010, 11:10 AM

You could use the formula:

=TEXT(A1,"m/d/yy ") &"Week"

Regards,
Antonio

**HP RodNuclear** · 12-20-2010, 11:17 AM

Thanks Antonio. That worked great. Now, would you know a way in Visual Basic that I could automate this process so that it would do that every 8th cell but be referring back to the first cell in it's series?

**antoka05** · 12-20-2010, 11:38 AM

You could use this code. Remember to set the year you need:

Please Login or Register  to view this content.

Regards,
Antonio

**HP RodNuclear** · 12-20-2010, 12:24 PM

This is amazing. That is exactly what I needed. However, if I wanted to this to go into the next year, how would I change the loop to keep going into the next year. I'm still new to this programming gig, but I imagine something needs to be added so that the loop will continue UNTIL the 'myyear' is reached.

**HP RodNuclear** · 12-20-2010, 12:31 PM

Also, what if I wanted to space the groupings by month? For example, right now, it lists the days and the 'week' portion exactly how I want it but I wanted to put some empty rows (4 to be exact) in between each month, so that as it goes through the looping, once it gets to the end of that month, it will stop, add 4 rows and continue as normal with the loop, including the formatting.

**Leith Ross** · 12-20-2010, 12:39 PM

Hello HP RodNuclear,

It appears that Antonio has provided you with a solution to your original question. Your other questions are about formatting your worksheet. You should start a new thread to keep the posts relavent to your original question.