Date Calculations not working

[HuwGro]

Hi

+$d$8 (where the date is in a date formated field)
+(g17x7) does not work.
G17 is weeks post award. 7 (days of the week) all I keep getting is value. I tried in a different cell +$d$8+14 and that failed too.

Anyone know why? I get the usual hopeless #value MS message.

Thanks

 

[arthurbr]

Is your date in D8 a real date or text looking like date ? ( left aligned by default - formatting does not change the underlying value)

 

[joeu2004]

"Text that looks like a date" can be used in arithmetic expressions just fine.

The real question is: does Excel recognize the cell value as a date?

First, yes, does ISNUMBER(D8) return TRUE? If yes, Excel recognizes it as a date.

If not, does ISNUMBER(--D8) return TRUE? If no, Excel does not recognize it as a date.

There can be many reasons why not. One likely reason: the "date" might be written m/d/yyyy form, but your system expects d/m/yyyy. An ambiguous date like 1/2/2019 might seem to work; but it would be interpreted as Feb 1, not Jan 2. However, 1/23/2019 would not work because 23 cannot be a valid month number.

Another likely reason: the data entry contains extraneous characters. Extraneous blank spaces (ASCII 32) are okay. But extraneous non-breaking spaces (ASCII 160) are not okay. Non-breaking spaces look like regular blank spaces. They commonly come from copying from a webpage.

 

[MARK858]

"Text that looks like a date" can be used in arithmetic expressions just fine.

The above statement needs something like "with coercion" added to it as is done with the 2nd test

 

[joeu2004]

"Text that looks like a date" can be used in arithmetic expressions just fine.

The above statement needs something like "with coercion" added to it as is done with the 2nd test

Not sure what you mean.

I meant that "1/1/2019"+14 calculates the date 1/15/2019 just fine (if dates are MDY), without any explicit conversion ("coercion"?). The cell format might need to be changed in order to see the result as a date.

However, I should emphasize that that is only true in arithmetic expressions, like HuwGro asked about. In particular, it is not true of comparisons. For example, "1/15/20"=DATE(2019,1,15) returns FALSE.

And I should reiterate that my point was: the problem would not be that "text looks like a (proper) date", but that text does not look like a proper date.

I am not suggesting that writing dates as text is a good idea.


PS....

The above statement needs something like "with coercion" added to it as is done with the 2nd test

If by coercion, you are talking about the first "+" in +$d$8+14.... No, that does not coerce date text to a numeric date. For example, +"1/15/20"=DATE(2019,1,15) still returns FALSE.

OTOH, --"1/15/20"=DATE(2019,1,15) returns TRUE. But there is nothing sacrosanct about the double negate. It is just that it creates a proper arithmetic expression. Apparently, one or more unary "+" does not, in this context.

 

[MARK858]

Hi joeu2004, sorry for taking so long for responding but amongst other things I have been doing some testing.

The reason I made my remarks was a long time ago I had a file that although the dates looked like UK dates the only way I could get any arithmetic to work was to convert the dates. I am sure that I used Value (as I tended to at that time) rather than the double negate but that is of no real relevance.

I don't have the file these days to look at again to figure out what was strange about it unfortunately.
With the testing today all the tests with text dates have been working fine and so there must have been something else going on with the file.

As this is the case I accept that I must be wrong about the need to convert the text dates.... apologies for wasting your time

 

[joeu2004]

The reason I made my remarks was a long time ago I had a file that although the dates looked like UK dates the only way I could get any arithmetic to work was to convert the dates. I am sure that I used Value

As I wrote before: ``The real question is: does Excel recognize the cell value as a date? [....] likely reason [why not]: the data entry contains extraneous characters``.

Yes, VALUE is more forgiving of some errant (regular) blank spaces than the internal Excel interpreter that converts numeric text in arithmetic expressions. In fact, I originally wrote VALUE(D8) instead of ISNUMBER(--D8) for that reason.

Example: "1 / 2 / 2019 " without quotes (any number of spaces everywhere except before 1/...) is interpreted as a numeric date. And if that were entered as text somehow (e.g. due to the original cell format), it would be converted to a numeric date automagically in arithmetic expressions like HuwGro's.

But " 1 / 2 / 2019 " and " 1/2/2019" without quotes (spaces before 1/...) is interpreted as text. If the latter is in D8, --D8 returns #value . But VALUE(D8) returns the correct numeric date serial number.

[ I believe the name "Excel" means "inconsistent" in some language in our solar system. (wink) ]

But then I remembered the point that I was trying to make, to wit: to discover why Excel did not treat the data entry as a numeric date, in the first place. It is not simply because it is "text that looks like a [proper] date".

So I chose the less lenient method of (automagic) conversion in order to identify the root cause of the problem, namely: ``the usual hopeless #value `` error.

I don't think that dwelling on such minutiae is a "waste of time". I think it's helpful to understand these nuances, so that we learn why things seem to work "sometimes", but not other times.

 

[HuwGro]

Screen shot

		AWARD DATE	01/02/2020
		COMPLETION DATE	30/9/2020
Required X = Yes	SDI Code	Description (Read in Conjunction with xxx Specification)	NOTES	Tender	FOR ACCEPTANCE	CERTIFIED	Contractual Date (Formulae)	Cerfified Final Due (Formulae)
Info Required	INFO REQ	Weeks Post Award	INFO REQ	Weeks Post Award
	CONTROL DOCUMENTS
X	V000	Other Items Provided by Vendor - not on SDRL List
X	00	Other Items Provided by Vendor - not on SDRL List
X	A01	Supplier Document Schedule [SDS]	CVD	PDF	PDF	2	PDF	8	#VALUE!	#VALUE!

<colgroup><col span="2"><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Date fields D6 and D7 do not appear to be being accepted as dates by Excel for the calculation.

"Text that looks like a date" can be used in arithmetic expressions just fine.

The real question is: does Excel recognize the cell value as a date?

First, yes, does ISNUMBER(D8) return TRUE? If yes, Excel recognizes it as a date.

If not, does ISNUMBER(--D8) return TRUE? If no, Excel does not recognize it as a date.

There can be many reasons why not. One likely reason: the "date" might be written m/d/yyyy form, but your system expects d/m/yyyy. An ambiguous date like 1/2/2019 might seem to work; but it would be interpreted as Feb 1, not Jan 2. However, 1/23/2019 would not work because 23 cannot be a valid month number.

Another likely reason: the data entry contains extraneous characters. Extraneous blank spaces (ASCII 32) are okay. But extraneous non-breaking spaces (ASCII 160) are not okay. Non-breaking spaces look like regular blank spaces. They commonly come from copying from a webpage.

 

[HuwGro]

I have the date format at dd/mm/yyyy in numbers and have the format as English Australian. If I reverse the data entry eg 2020/02/01 the formula works....

Screen shot

		AWARD DATE	01/02/2020
		COMPLETION DATE	30/9/2020
Required X = Yes	SDI Code	Description (Read in Conjunction with xxx Specification)	NOTES	Tender	FOR ACCEPTANCE	CERTIFIED	Contractual Date (Formulae)	Cerfified Final Due (Formulae)
Info Required	INFO REQ	Weeks Post Award	INFO REQ	Weeks Post Award
	CONTROL DOCUMENTS
X	V000	Other Items Provided by Vendor - not on SDRL List
X	00	Other Items Provided by Vendor - not on SDRL List
X	A01	Supplier Document Schedule [SDS]	CVD	PDF	PDF	2	PDF	8	#VALUE!	#VALUE!

<tbody>
</tbody>

Date fields D6 and D7 do not appear to be being accepted as dates by Excel for the calculation.

 

[joeu2004]

I have the date format at dd/mm/yyyy in numbers and have the format as English Australian. If I reverse the data entry eg 2020/02/01 the formula works

Screenshots are almost useless for troubleshooting problems of this nature, since the problem might be an invisible extraneous character (or errant blank space) that causes Excel not to recognize the data entry as a numeric date.

Moreover, the cell format does not matter, since it does not apply to text. In any case, the cell format only affects the appearance of a numeric value. It does not affect how data entry is interpreted.

The regional and language settings for your computer determine, in part, how Excel interprets data entry. Even though the language might be English Australian, you can override the default short date data-entry form (d/m/y).

It is also possible to set a language option just for Excel; in Excel 2010, I click File > Options > Language. I don't know if that affects the interpretation of data entry, overriding regional and language settings. But I also think that is not a likely cause of your problem.

The fact that 2020/2/1 works suggests that one language setting or another specifies that the short date data-entry form is y/m/d or y/d/m.

Given that, when you enter 1/2/2020, Excel tries to interpret 2020 as a day or month number. Since the year is obviously not valid as such, the data entry is interpreted as text.

Obviously, the long-term remedy is to track down the source of the unintended short date data-entry form and correct it.

But a short-term solution might be to convert text that Excel does not recognize as dates.

-----

A simple method that might work is to use Text To Columns to convert the data-entry form.

In Excel 2010, I select the cells (column range), then I click Data > Text To Columns. Work through the first two dialog boxes. In the third and final dialog box (with the Finish button), select the column to be converted, click on the radio button next to Date, select the appropriate form of the __data_entry__ text to be converted (DMY), then click Finish.

I believe TTC will choose a default cell format. So, you might have to select the cell format that you want again.

----

If TTC does not work for you, you will have use formulas to do the conversion.

In order to eliminate any confusion between what you see and what Excel expects (I speculate), I suggest that you use the DATE function. For D6:

=DATE(RIGHT(D6,4),
REPLACE(REPLACE(D6,1,FIND("/",D6),""), FIND("/", REPLACE(D6,1,FIND("/",D6), "")), LEN(D6), ""),
 LEFT(D6,FIND("/",D6)-1))

Copy the cell with that formula, and paste-special-value into D6. Then clear the cell with the formula.

And again, you might have to select the D6 cell format that you want again

-----

If neither of those methods works, please upload an example Excel file that demonstrates the problem to a file-sharing website, and post the download URL in a response here. I like box.net/files. Please test the download URL to ensure that it works when you are not logged into the file-sharing website.

Some participants might object because they cannot or will not download external Excel files. But if neither of the methods above works, the devil might be in details that we can only see in the Excel file.