When having to compile the text from different cells, I am getting issue with dates converted to numbers. I have 3 cells with patient number, kit number and date (formatted to dd-mmm-yyyy). Tried different formatting and formulas but still cannot get the result 1_1234_26-JUL-2021, the dates keep changing to numbers.

1 Answer

It's not entirely clear, but it seems like you have a formula like this:

=A2&"_"&B2&"_"&C2

... where A2 is a patient number, B2 is a kit number, and C2 is a date. You expect C2 to be pulled in with the same formatting but it's showing up as a number, probably 44403 if you're on Windows and the date is 26-JUL-2021. This is because Excel treats dates (and times) as numbers. For windows, it counts the days from 01-JAN-1900. If you reference a date in a formula, it uses the number. There's a way to force it to whatever format you want, though:

=A2&"_"&B2&"_"&UPPER(TEXT(C2,"dd-mmm-yyyy"))

TEXT formats a number as desired and returns the result as a string. UPPER will make it return JUL instead of Jul.

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy