Spreadsheet date problem

Question

I have a spreadsheet with 35 rows. 2 columns.

I have an IF formula in cell 36 =IF(B2="","",A2&": "&B2&".") So, if there is no data in Cell B2, my formula cell remains blank. If i have something in cell B2, it puts A2 in front of B2 and provides an answer.

Im using this for many cells and it's been fine for years now. I now changed the FORMAT of B2 to reflect the date differently than how i type it. i type 7-30-2020 and formatting changes it to Thursday, July 30, 2020 for me. Great! But in my formula cell (A36) it no longer works.

Before it would read "(A2) Date is (B2) 7-30-2020" now that i formatted that date cell it's reading "(A2) Date is (B2) 44043". Why isn't it reporting the correct version of the date?

Thanks

Answer 1

Dear Chuck,

Excel treats dates as Number while formatting is only for visual display to us.

What you are encountering is due to change in format of the cell to Date specific function.

Please try below formula. I hope it works out for you.

=IF(B2="","",A2&": "&TEXT(B2,"dddd")&", "&TEXT(B2,"mmm")&" "&DAY(B2)&", "&(YEAR(B2)))

Additionally it will be safe to use the formula given above as it will make outcome independent of format of source cell :):):)

Answer 2

Hi ChuckHenrickson,

I noticed the same thing. Meanwhile, please allow me sometime, I'll try to find the solution to this problem.

Thanks for your patience.

Regards,

Neha