Date not being recognized

I'm working with data extracted from a system which expresses the date as the short weekday - calendar day, so for example it may just have Mon-01 and Sun-30. It's obviously impossible for excel to translate this as a date since it is only relative to the date it is extracted, there is no way to know which month "Sun-30" is the 30th for. I'm trying to write a macro to automatically format and slice this data as needed every time it is extracted. The only way I've found to give the date some meaning is to create a new column and do left(cell,3) which then just gives me the day of the week, which is fine. But the problem is the macro doesn't automatically do this for each data pull because the number of rows may be different and I don't know how to make it automatically calculate for all rows. Is there some other way to tell excel what this data means so that it understands and can process it?

Hi,
I'm not sure what you mean by what code? The only code I have tried, which works but not in my macro, is left(cell,3), which then gives me Sun, Mon, Tue, etc. Excel then understands what these terms mean in the sense that I can sort and it will not do alphabetical sort but sort by day of the week.

Actually, I think I have found a work around to my problem, if I convert the data to a table format then whatever I put in the first row is copied to all other rows automatically it seems regardless of the number of rows. So that may just work for my macro.

yes, I'm not that advanced, I only hit record and let the macro code write itself. I don't really understand how to do anything beyond that with macros. What I was doing was writing the formula in row 1 and then double clicking the handle to populate all the other rows with the same data but the macro was interpreting that to literally do how ever many rows were in that data set. So if the macro I used to record with had 50 rows and then I open a file that has 51, it would not copy the formula down past row 50. However, it appears that if I first covert my data to a table and then enter the formula in row 1 it automatically fills the other rows regardless of how many there are, so that solves the macro issue I think(?).