Convert DATEVALUE result to uppercase

Here’s how I ended up solving it.

By breaking the problem down into manageable parts and then re-assembling the results.

Here’s the layout with the Column TITLES:

|| A1 = WEEKDAY | B1 = DATE | C1 = TIME | D Blank | E = MONTH | F = DAY | G = YEAR ||

The only data entry I will do multiple times a day in my log is into Column C.

In cell C2, I enter the date as: 2/2/2021 9:15 AM

NOTE: Numbers accepts the date with or without leading zeros,

All I care about displaying in Column C is the Time of my last action.

For that, I use Numbers Format, Date and Time. I set Date to None and click on the 1:08 AM time format.

In cell A2, I enter = DAYNAME(DATEVALUE(C2)) to retrieve the weekday name from Column C’s date/time.

I prefer it in all caps for ease of reading, so I modify the formula to be: = UPPER(DAYNAME(DATEVALUE(C2)))

Before I can complete column B, I must have intermediate results from Columns E, F, & G.

In cell G2, I enter = YEAR(DATEVALUE(C2)) to extract the Year from cell C2.

In cell F2, I enter = DAY(DATEVALUE(C2)) to extract the Day from cell C2.

In cell E2, I enter = MONTHNAME(MONTH(DATEVALUE(C2))) to extract the Month from C2.

The MONTH() function returns a number from 1 to 12, but I need the name of the month,

so, I use the MONTHNAME() function to convert the month number to the month’s name.

Good, now I have all my pieces, I just need to assemble and format them, as I need them.

In cell B2, I enter = LEFT(UPPER(E2),3)&” “&F2&”, “&G2 which should display, FEB 2, 2021

E2 supplies the month’s fullname in Mixed case, so I use UPPER to get it all into uppercase.

Outside of that, the LEFT(cellref,3) function extracts the left 3 letters ofd the months full name.

I could have used the UPPER() and LEFT() functions in cell E2 formula, but then it’s harder to write and read later.

The ‘&’ symbol is used to combine our own text with the cell contents. AKA Concatenation

I used the ‘&’ symbol to add spaces and a comma into the final result.

If ONLY I hadn’t wanted ‘Feb’ to appear as ‘FEB’,

I could have simplified the heck out of this with B2 = DATEVALUE(C2) and used Numbers’ Format, Date and Time.

Everytime I look for a solution, rather than take the easy way out, I learn something.

Hope this example helps some out there.

CORRECTIONS TO MY SOLUTION:

My use of DATEVALUE() in the formulas yields incorrect results. My apologies for any confusion this may have caused.

WRONG: In cell A2, I enter = DAYNAME(DATEVALUE(C2)) to retrieve the weekday name from Column C’s date/time.

RIGHT: In cell A2, I enter = DAYNAME(C2) to retrieve the weekday name from Column C’s date/time.

WRONG: I prefer it in all caps for ease of reading, so I modify the formula to be: = UPPER(DAYNAME(DATEVALUE(C2)))

RIGHT: I prefer it in all caps for ease of reading, so I modify the formula to be: = UPPER(DAYNAME(C2))

WRONG: In cell E2, I enter = MONTHNAME(MONTH(DATEVALUE(C2))) to extract the Month from C2.

RIGHT: In cell E2, I enter = MONTHNAME(MONTH(C2)) to extract the Month from C2.

WRONG: In cell F2, I enter = DAY(DATEVALUE(C2)) to extract the Day from cell C2.

RIGHT: In cell F2, I enter = DAY(C2) to extract the Day from cell C2.

WRONG: In cell G2, I enter = YEAR(DATEVALUE(C2)) to extract the Year from cell C2.

RIGHTT: In cell G2, I enter = YEAR(C2) to extract the Year from cell C2.