Date Time Data - convert to date only?

[Sunshine8790]

I have headers in row 1, so all my data starts in row 2.
In column AG, specifically beginning in AG2, I have data showing in this format: Jan 1, 1998 12:00:00 AM
In AH2 I have:

=TEXT(AG2,"dd/mmm/yyyy")

It displays: Jan 1, 1998
All good so far, except that I need to find a way to separate the month and day in the next 2 cells.
So in AI2 I want it to display "Jan", and in AJ2 I want it to display "1"

However, when I try

=IF(AH2="","",MONTH(AH2))

in AI2, it displays "1". Technically correct as January is the 1st month but I want it to say Jan.

Any help on this one would be appreciated.

 

[Joe4]

The TEXT function returns a string. Since it is no longer a date, you can no longer do date functions on it, unless you convert it back to a date.
But there shouldn't be any need to. If you want to keep it a date, just format your original cell (AG2) to a date format of "dd/mm/yyyy", and it will not display the time piece.

 

[Fluff]

How about

=TEXT(AG2,"mmm")

 

[Sunshine8790]

How about

=TEXT(AG2,"mmm")

You're my hero!
Thank you!

 

[Sunshine8790]

The TEXT function returns a string. Since it is no longer a date, you can no longer do date functions on it, unless you convert it back to a date.
But there shouldn't be any need to. If you want to keep it a date, just format your original cell (AG2) to a date format of "dd/mm/yyyy", and it will not display the time piece.

I did try this, but it displays the date like 01/01/1998. I wanted to keep the original format minus the date , appreciate you though! Thank you.

 

[Joe4]

I did try this, but it displays the date like 01/01/1998. I wanted to keep the original format minus the date , appreciate you though! Thank you.

That was a typo on my part - it should be "mmm", not "mm".

Note that the time of 12:00 AM is the same as 0 for the time component.
Dates and times are in Excel are really just numbers, specifically the number of days since 1/0/1900.
And time is just the fractional component of one day.

So all that dates are in Excel are numbers with special date/time formats.
You can see this really easily by entering any date, and then changing the format of that cell to General.
Then you see the date/time as Excel does. Formatting just changes the display in the cell, not the actual value.

 

[Fluff]

Glad we could help & thanks for the feedback.