Compare dates with today()...

I know I can compared a date with today() and perform some action. What I'd like to do is do the same thing if the dates (as typed in the cells) are in the following format (the cell is formatted as General):

Dec. 21 - Jan. 4 '07
Jan. 8
Jan. 13-16
Jan. 20 - Mar. 4

I suppose some parsing would have to be performed on that string to figure out what the date is, but I don't know how to achieve that - nor if it's even possible.

But, what I like to do is figure out if today() is later than whatever is in the cell, and if so change the text color in the adjacent cell.

Originally Posted by AMK4

I know I can compared a date with today() and perform some action. What I'd like to do is do the same thing if the dates (as typed in the cells) are in the following format (the cell is formatted as General):

Dec. 21 - Jan. 4 '07
Jan. 8
Jan. 13-16
Jan. 20 - Mar. 4

I suppose some parsing would have to be performed on that string to figure out what the date is, but I don't know how to achieve that - nor if it's even possible.

But, what I like to do is figure out if today() is later than whatever is in the cell, and if so change the text color in the adjacent cell.

Hi AMK4,

I copied your data into A1, then Data > Text to Cells, Step 1 Delimited > Next
Step 2 select Tab, Space, Other and insert -, then Finish

Then coverted to proper dates, see attached spreadsheet.

oldchippy

The dates I provided are all in 1 cell. Basically the sheet is build with column A containing the date strings, column B containing text.

What's in column A is what I gave as example. Your sheet has it all broken up into separate columns. Mine are all in one cell.

Hi,

Oldchippy gave you the answer in hi answer ...
Data TexttoColumns will parse your data ...


HTH
Carim

Originally Posted by AMK4

The dates I provided are all in 1 cell. Basically the sheet is build with column A containing the date strings, column B containing text.

What's in column A is what I gave as example. Your sheet has it all broken up into separate columns. Mine are all in one cell.

Hi AMK4,

I did as you asked in your original question

I suppose some parsing would have to be performed on that string to figure out what the date is, but I don't know how to achieve that - nor if it's even possible

I copied your data into A1, then Data > Text to Cells, Step 1 Delimited > Next
Step 2 select Tab, Space, Other and insert -, then Finish

Then coverted to proper dates,

Are you Ok with that, or do you need further help?

oldchippy

Okay so I figured out what you were trying to show me, the actual parsing. What I didn't realize was that this would take up several cells in a row on the sheet. So I think what I'd like to do then is make a macro that runs through Worksheet_Activate() and it just runs down the column containing the dates, does the parsing, then changes the text color on the adjecent column.

My problem is, I don't know how to get the "Text to Columns..." menu function into macro code. Do I just grab the cell contents and split it, mimicking what the function does?

Any help here would be appreciated.

Hi,

Often using the macro recorder provides a good starting point ... and helps also in the learning/ testing process ...

HTH
Carim

I did, and realized that unless I came up with a better convention to write the dates, the macro becomes this big beast for what seems to be a rather simple task.

So for now I've dropped this from the TODO list (or at least moved it to the end) and will, perhaps some day, go back to it (I have bigger fish to fry right now.) Part of the complication is exactly the way the dates are written and I need to fix that.