Excel Date Appears as Date, Says It's Formatted as Number

Today, Excel 2016 (on Windows 10) greets me with this:

Clipboard01.png

As that image indicates, Excel is displaying a date-time combination in D3, and yet Excel says D3 is formatted as a number. I think it should say that it is displaying a date or time format. That is issue no. 1.

When I use Format Painter to copy the formatting of D3 to D2, I get 44767, as displayed. That, I agree, is formatted as a number.

The formula in D2 is this: =B2-C38/24/60
where C38 presently contains the number 71.

That formula, and Format Painter, would lead me to expect that D2 would say "07/24/2022 12:32 PM." That is, I think D2 should state a time that is 71 minutes less than B2, and I think the time should be displayed in the same format as D3. That is not what appears in D2. That is issue no. 2.

So, two issues. What am I getting wrong here?

Thanks for that humane response, Glenn. Sorry: I got out of bed, had fog in my eyes, fog in my head, and an uncooperative Excel. Reading a yellow banner was apparently beyond my capacity at the moment. Let's try this again.

Hopefully I have the sample spreadsheet attached this time.

The dates are being seen as Text regardless of how it is being formatted. This usually happens when you copy/paste from somewhere else into Excel.

You can quickly tell if a cell is being seen as Text by removing any alignment and seeing if it is left aligned in the cell.
Also, =ISTEXT(B3) will return TRUE when B3 is being seen as Text.

You want to get Excel to recognize that they are in fact dates.
You could change the column formatting and then double click on each cell to get them to format as a date (or number which you can then format as a date), but the quicker way to do this is by using Text to Columns.

Highlight column B > Data > Text to Columns > Delimited > Finish
You can now format columns B and D as a date.

Hi,

Since I am seeing the Date as Text Format, I have written a formula to achieve your requirement. Try using this and check if this works for the Issue No.2.

=CONCATENATE(LEFT(B2,10)," ",TEXT(TIME(HOUR(TIMEVALUE(RIGHT(B2,8))-C38/1440),MINUTE(TIMEVALUE(RIGHT(B2,8))-C38/1440),SECOND(TIMEVALUE(RIGHT(B2,8))-C38/1440)),"hh:mm AM/PM"))

Hi,
Attached File for your reference. I have followed following steps to achieve the requirement. First to split the Date/time Stamp into Date and time separately and then changing it to the required format works in this case.

Step 1: Select the cells from B2 to B36, Go to Data>Text to Columns>Fixed Width
Step 2: Click Next and Remove the break line between Time and AM/PM.
Step 3: Click Next & Select the Date option and change the destination to G2 and click Finish
Step 4: Now, G2 and H2 will have the date and time respectively. Add Formula in I2 as =G2+H2.
Step 5:Go to the format cells>Custom Change the format of G2 as mm/dd/yyyy, H2 as hh:mm AM/PM and I2 as mm/dd/yyyy hh:mm AM/PM.

I have copied the Formula Column C and D formula to Column J and K. Try this option and see whether this works for you.