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

so here is the exact formula i'm using. i'm doing this for 36 different cells at once, and again it all works. but when i request a date it doesnt work.

i made a new sheet with just one formula. still broken

cell A9 is =IF(B2="","",A2&": "&B2&".")

Cell A2 = "date"

Cell B2 = "7-31-2020"

Cell A9 should return "date: 7-31-2020"

I then formatted B2 to change date format to read day, month, date, year so when i type in quickly 7-31-2020 it READS "Friday, July 31, 2020" in hopes that cell B2 would return "Date: Friday, July 31, 2020" but it doesn't.  it's returning "Date: 44043"

Answer 3

Thanks for the reply Jeovany! unfortunately that doesn't work. I tried that already. No amount of formatting changes the answer. the source cell is formatted, and answer cell is formatted. answer is still 44043 and not the date as intended.

Here's a new question. Is there a format within the formula? like because of the formula, the answer is defaulting to "general format" setting or something? not sure if im explaining it correctly but it's just another thought. 

Thanks again

Answer 4

Hi Chuck

Please, change the cell formatting in cell A36 to "Long Date" to convert 44043 to a date value displayed as "Friday 31 July 2020"

I hope this helps you

Regards