Help With DateValue and upper / lower case

[maximillianrg]

Hello Excel Masters

Cell F1 Formula: =DATEVALUE(LEFT(TEXTAFTER(K1,"ETA: "),11))
Cell K1 Content: Deployment schedule ETA: 15-APR-2024 and we plan to etc.......
Cell F1 Results: 15-Apr-2024

I need the formula in cell F1 to return the month in cell K1 in the exact same case it is in K1.

The month is listed as APR but the formula return it as Apr
If the month was ApR I would need the formula to return Apr

Any thought on how to do this and thank you in advance

 

[MARK858]

Have a look at the thread below from yesterday where we discuss having the month as capitals, basically if you want it via formula then the result will be text and not a real date.

Excel number format help

Hi all, first post. I am trying to format a range of cells so that when a user enters a date as 12/21/23, it appears in the cell as 21-DEC-23. There is a number format that results in 21-Dec-23, but I am wanting the month in CAPS. I have tried to use a custom format of d-MMM-yy, but that...

www.mrexcel.com

 

[maximillianrg]

Thank You Mark, unfortunately I am trying to get a team of people to standardize on a date format where the month is in all caps so I need the date pulled from cell K1 to be accurately displayed in F1. For example

if M1 contains 08-DEC-2023 then F1 also needs to display as 08-DEC-2023
if M1 contains 08-Dec-2023 then F1 also needs to display as 08-Dec-2023

The hooks is I need these dates as numbers so I can do date calculations

 

[RoryA]

You can't have both (at least not in the same cell) other than with the all caps font.

 

[awoohaw]

Thank You Mark, unfortunately I am trying to get a team of people to standardize on a date format where the month is in all caps so I need the date pulled from cell K1 to be accurately displayed in F1. For example

if M1 contains 08-DEC-2023 then F1 also needs to display as 08-DEC-2023
if M1 contains 08-Dec-2023 then F1 also needs to display as 08-Dec-2023

The hooks is I need these dates as numbers so I can do date calculations

The hooks is I need these dates as numbers so I can do date calculations

If you want cell F1 to have the case as in cell K1, why put the DATEVALUE function in it.
You can convert it to date with the DATEVALUE function in your subsequent formulas.