Date Calculations not working

HuwGro

New Member
Joined
Nov 1, 2019
Messages
3
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

Well-known Member
Joined
Dec 8, 2006
Messages
1,919
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,572
Office Version
2010
Platform
Windows
"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.
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,469
Office Version
365, 2010
Platform
Windows, Mobile
"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

Well-known Member
Joined
Mar 2, 2014
Messages
2,572
Office Version
2010
Platform
Windows
"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.
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,469
Office Version
365, 2010
Platform
Windows, Mobile
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,572
Office Version
2010
Platform
Windows
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.
 
Last edited:

HuwGro

New Member
Joined
Nov 1, 2019
Messages
3
Screen shot

AWARD DATE01/02/2020
COMPLETION DATE30/9/2020
Required X = YesSDI CodeDescription (Read in Conjunction with xxx Specification)NOTESTenderFOR ACCEPTANCECERTIFIEDContractual Date (Formulae)Cerfified Final Due (Formulae)
Info RequiredINFO REQWeeks Post AwardINFO REQWeeks Post Award
CONTROL DOCUMENTS
XV000Other Items Provided by Vendor - not on SDRL List
X00Other Items Provided by Vendor - not on SDRL List
XA01Supplier Document Schedule [SDS]CVDPDFPDF2PDF8#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

New Member
Joined
Nov 1, 2019
Messages
3
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 DATE01/02/2020
COMPLETION DATE30/9/2020
Required X = YesSDI CodeDescription (Read in Conjunction with xxx Specification)NOTESTenderFOR ACCEPTANCECERTIFIEDContractual Date (Formulae)Cerfified Final Due (Formulae)
Info RequiredINFO REQWeeks Post AwardINFO REQWeeks Post Award
CONTROL DOCUMENTS
XV000Other Items Provided by Vendor - not on SDRL List
X00Other Items Provided by Vendor - not on SDRL List
XA01Supplier Document Schedule [SDS]CVDPDFPDF2PDF8#VALUE!#VALUE!

<tbody>
</tbody>

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

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,572
Office Version
2010
Platform
Windows
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:

Code:
=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.
 

Forum statistics

Threads
1,081,677
Messages
5,360,451
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top