Have date and text in same cell, but still have cell function as date for formulae

[ExcelNew7]

Hi,

In cell B4 and C4 I have dates. I have formulae in another part of the spreadsheet which calculate things depending on those values.

I want C4 to display the word to and a space before the date, so B4 and C4 across read for example 22/07/2024 to 31/07/2024. However, C4 must still function as a date itself so a formula would view it as 31/07/2024 rather than the text 'to 31/07/2024' as then my formulae would not work.

How can I achieve this please? It must be done in the single cell.

Thank you

 

[6StringJazzer]

It's not a good practice to store a date as text that would then have to be decoded back into a date, as you will see shortly. Why does it have to be done in a single cell?

The expression to extract the date would look like this:

DATEVALUE(RIGHT(C4,10))

However, I am using US localization. My Excel won't work with the date format you provided. I don't know if Excel will correctly convert dates in the format you have provided, even if you use non-US localization.

An alternative that does the work explicitly would be

DATE(RIGHT(C4,4),MID(C4,7,2),MID(C4,4,2))

 

[NoSparks]

I believe I've seen this done before with custom formatting.
Type: To d/m/yyyy

 

[ExcelNew7]

It's not a good practice to store a date as text that would then have to be decoded back into a date, as you will see shortly. Why does it have to be done in a single cell?

The expression to extract the date would look like this:

DATEVALUE(RIGHT(C4,10))

However, I am using US localization. My Excel won't work with the date format you provided. I don't know if Excel will correctly convert dates in the format you have provided, even if you use non-US localization.

An alternative that does the work explicitly would be

DATE(RIGHT(C4,4),MID(C4,7,2),MID(C4,4,2))

Thanks for your reply. It is because I have three cells in which to fit 'Rate from:', '22/07/2024', 'to' and '31/07/2024' before I go into a cell that would affect other formulae.

I believe I've seen this done before with custom formatting.
Type: To d/m/yyyy

that seems to have worked - thank you very much