Date formatting issue

Topic

New Reply

Attached is a spreadsheet with some copied data. For some reason there are some cells that will not format like the others which is messing up a formula I have in another column. In Column A, all cells are formatted as General in order for the formula in Column H to work. Some specific cells (colored purple in the attachment) just refuse to cooperate and I am out of ideas on what to do. All help will be appreciated.

Thanks.

Reply | Quote

Replies

It seems to help if you change to a consistent date format.

The top cells in col A are formatted in mm/dd/yyyy format : e.g. 05/25/2006

But A5 uses dd/mm/yyyy format : 25/10/2007.

Reply | Quote

Hmm, I tried that on my end without any luck. The cells with the ‘correct’ format were actually formatted as ‘General’ on my end in order to work with the formula. I tried switching them all to the mm/dd/yyyy format but it did not help on the formula end (at least for me) with the problem cells. The other cells seem to be okay either way.

Thanks.

Reply | Quote

For whatever reason the problem cells are formatted as text for excel. To fix this:
Goto a blank cell
Edit – COpy
Select column A
Edit – paste special – Add [ok]
Format column A as desired

Steve

Reply | Quote

Tried that too. After I do the paste and then format, the problem date cells convert to the numeric value for the date. The ‘okay’ cells are still fine. I’m honestly baffled. No clue what is going on with these cells.

Thanks.

Reply | Quote

Once you have the numberic numbers you can format as desired. Before this conversion, the cells have text and not the numbers. The values in the cells need to be teh numeric values for the formatting to work

Steve

Reply | Quote

Hello – Could you left justify Column A for the appearance you want?

Try this formula in Column H
I used this in cell H5, and then copied it to all other cells in Column H
=IF((F5/G5)*(2009-YEAR(A5))>F5,F5,(F5/G5)*(2009-YEAR(A5)))

Tim

Reply | Quote

Appreciated! I tried the formula and the left justify and my totals changed. But thank you so much for trying! I think I’m going to have to go with the manual effort with this entire workbook since I can’t figure out what is going on with that column.

Thank you all for the assistance.

Reply | Quote

Hello – Can you post a worksheet that shows the correct answers that you want in Column H? Use Column J (if convenient) to show what the answers you want are. Then post the worksheet here.

What should Column H be showing? What is the logic?

Tim

Reply | Quote

Try putting a ‘ , single quote, in front of the date in the formula bar. There seems to be some type of formatting at the beginning of the contents of the cells in column A that is allowing the other cells to format as General and keep the date as you want. The reason I say something is at the beginning, If I go to say, A1 and at the start of the date hit backspace, it will result in what you have in the purple shaded area.

After you put in the single quote you can format the as text and then eliminate the single quote if you want. Then you can format as General and it will keep the date as the other cells. Hope this helps.

Tim M.

Reply | Quote