Date formatting not working

[ianharper68]

Ok I do not know what is happening but for some reason I am completely unable to format a column of cells? I have deleted the column and re-added it and still it wont format? I have cleared formatting, saved the worksheet, closed it and re-opened it, still won't format?

Can anybody help?

It is the "Date Goods In" column and if the date format doesn't work then neither does my calculation in column "Chargeable Days"

PE0001 - EDCR Master Worksheet copy.xlsm
	AB	AC	AD	AE
7	Date Goods In	Date Goods out	Free Days	Chargeable Days
8	15/12/24	30/01/2025	3	#VALUE!
9		30/01/2025	3
10		30/01/2025	3
11		30/01/2025	3
12		30/01/2025	3
13		30/01/2025	3
14		30/01/2025	3
15		30/01/2025	3
MASTER SHEET

Cell Formulas
Range	Formula
AC8:AC15	AC8	=Dashboard!$Q$24
AD8:AD15	AD8	=$AD$3
AE8:AE15	AE8	=IF(AB8="","",AC8-AB8-AD8)

 

[Joe4]

See all those little triangles in the upper right-corner of all the cells in column AC?
That indicates that all those entries are actually text and not dates.
Re-format column AC to a date format, and then re-enter those values (you can do all at once using "Text to Columns").
Once there are valid dates in column AC, then you can use that column in mathematical computations.

 

[ianharper68]

Column AC is not the issue that is in date format. The problem column is AB ... even if I drag a cell from AC to AB it is fine and the formula works and I get -3. But if I try and change the date in the same cell it reverts back to the previous

Here is the screen shot of AC with its format:

Here is a screen shot of the copied cells from AC to AB .... as you can see the calculation works fine as Date goos In - Date Goods out - 3 days = -3 which is correct:

Now here it the same cells in AB showing they are in fact formatted to the date correctly:

However as soon as I try and change the date the formatting is lost (although it is showing as correctly formatted in cell format)?

 

[Joe4]

I am a bit confused by your explanation (I am not quite sure what you are trying to tell me).

But allow me to explain a few things, and see if it helps you pinpoint your issue.

In Excel, all valid dates are really stored as numbers, specifically the number of days since 1/0/1900.
You can easily see this by entering a valid date in any blank cell, and then converting the format of the cell to Number or General.
Then you will see the date as Excel sees it.
So if you enter "30/01/2025" into any blank cell and change the format to Number or General, you should see 45687.

That is helpful to know. That means that you can easily verify if an entry is text or a valid date entry by using the ISNUMBER function.
In your workbook, if you enter the following formulas in any blank cells, they should ALL return TRUE.

=ISNUMBER(AB8)
=ISNUMBER(AC8)
=ISNUMBER(AD8)

If any of them returns FALSE, you are dealing with a Text entry, and not a valid Numeric/Date entry, and you won't be able to do any math on it until you convert it.
And simply changing the format of cells with existing values in them will NOT make that change! The data needs to physically be re-entered.

Note for values that you are pulling by formula from other sheets (i.e. =Dashboard!$Q$24), changing the format of the cell and re-entering the formula will not fix it, because the issue is at the source (the Dashboard) sheet. So you would either need to fix the issue with those values right on the Dashboard sheet, or convert the value to a valid date by updating that formula above to this:

=DATEVALUE(Dashboard!$Q$24)

The DATEVALUE function will convert a valid looking date entered as text to a valid date value.

 

[ianharper68]

Right so here is the conundrum? IF I open the file on my MAC the formatting and the calculations work absolutely FINE, however due to the drawbacks of excel for MAC with VBA and Macros, I also have Windows 11 on a Parallels Virtual Machine. When I open the same file in Excel in Windows 11 it won't work EXCEPT ONE CELL .....

This is Mac:

Same File in Windows the only cell that works is AB12:

BUT here is the kicker .... IF I copy that cell to all others it again seems to work fine UNTIL you try to change the date and then it reverts back to the old unformatted date??

It certainly is puzzling

 

[Joe4]

On the computer it is not working, it seems to suggest that it is not recognizing one of those columns as a valid date/number entry.
Pick one of those rows that is getting the #VALUE error, and check the value in all three columns used by the formula returning the error (AB, AC, and AD) like I showed in my last post, and see if they all return TRUE or any return FALSE.

It is important to do this on the computer returning the error, and on the row returning the error.
So if cell AE9 is reporting the "#VALUE error, you want to check:

=ISNUMBER(AB9)
=ISNUMBER(AC9)
=ISNUMBER(AD9)

Note that if it works on some machines and not others, I suspect there may be different setting/configurations causing issues, i.e. default date formats and regional settings.

 

[RoryA]

I suspect your regional settings are US on the VM. So 15/1/24 would not be a valid date (but 1/15/24 would).

 

[ianharper68]

I am a bit confused by your explanation (I am not quite sure what you are trying to tell me).

But allow me to explain a few things, and see if it helps you pinpoint your issue.

In Excel, all valid dates are really stored as numbers, specifically the number of days since 1/0/1900.
You can easily see this by entering a valid date in any blank cell, and then converting the format of the cell to Number or General.
Then you will see the date as Excel sees it.
So if you enter "30/01/2025" into any blank cell and change the format to Number or General, you should see 45687.

That is helpful to know. That means that you can easily verify if an entry is text or a valid date entry by using the ISNUMBER function.
In your workbook, if you enter the following formulas in any blank cells, they should ALL return TRUE.

=ISNUMBER(AB8)
=ISNUMBER(AC8)
=ISNUMBER(AD8)

If any of them returns FALSE, you are dealing with a Text entry, and not a valid Numeric/Date entry, and you won't be able to do any math on it until you convert it.
And simply changing the format of cells with existing values in them will NOT make that change! The data needs to physically be re-entered.

Note for values that you are pulling by formula from other sheets (i.e. =Dashboard!$Q$24), changing the format of the cell and re-entering the formula will not fix it, because the issue is at the source (the Dashboard) sheet. So you would either need to fix the issue with those values right on the Dashboard sheet, or convert the value to a valid date by updating that formula above to this:

=DATEVALUE(Dashboard!$Q$24)

The DATEVALUE function will convert a valid looking date entered as text to a valid date value.

So I carried out the ISNUMBER function as you said.

So here is the results with the cell copied from the single cell that seemed to work and I get all TRUE:

UNTIL I try to manually change the date: then it reverts to false:

So the formatting is not working as it should??

 

[Joe4]

Did you see Rory's reply?

 

[ianharper68]

I suspect your regional settings are US on the VM. So 15/1/24 would not be a valid date (but 1/15/24 would).

Thank you for some reason the Mac and Parallels hadn't updated although it was showing it the correct time zone ... all fixed now Thanks RoryA