Date Calc Not Working

Stacey Mordarski

I feel like I am going crazy here....

I have a formula in the first column (formatted as a number) to take 04/27/22 and subtract 04/17/22 (both columns are formatted as date columns) but the result is 14? When I take the 04/17/22 and add that same cell of "14" I get 05/01/22.

Has anyone else run into similar issues when calculating date differences and what should I do to fix it? (I am doing a ton of calcs based off of dates in my sheet and now I am worried that they are wrong!

Paul Newcome

Sometimes there is a little bit of a gremlin in the system and even though a date shows as 4/27/22 it will (because of the gremlin) be treated as a different date or possibly not even a date at all.

Let's check something else out too... Insert a temporary testing (text/number) column and in the same row as the troublesome date enter:

=IF(ISDATE([Date Column Name]@row), "date", "NOT A DATE")

What are the results?

Paul Newcome

I honestly have never run into that particular problem myself. I think that it is a random bug and not necessarily related to any particular function or process.

Paul Newcome

Did you double check that your formulas are referencing the correct cells? It sounds like either you accidentally grabbed the wrong cell reference in the first bit (which gave you 14 instead of 10). If that is not the case then it sounds like there may be some back end data being stored improperly in which case you will need to reach out to support.

Stacey Mordarski

Hi Paul,

Yep, I checked that the formulas are referencing the correct cells. I even re-created the formulas in another cell to see if I got the same result (that is the image I posted). I found another instance of it happening in my sheet and it seems to sporadically happen only when I do calculations on a date that is being pulled in via an index/match formula from another sheet (the 04/27/22 date).

Can you let me know what you mean when you said back end data may be stored improperly?

Thank you so much for your help!!

Paul Newcome

Sometimes there is a little bit of a gremlin in the system and even though a date shows as 4/27/22 it will (because of the gremlin) be treated as a different date or possibly not even a date at all.

Let's check something else out too... Insert a temporary testing (text/number) column and in the same row as the troublesome date enter:

=IF(ISDATE([Date Column Name]@row), "date", "NOT A DATE")

What are the results?

Stacey Mordarski

Hi Paul,

I already deleted all of the columns that weren't calculating correctly because the sheet is used company wide for scheduling operations and I didn't want anyone to use incorrect data but I will definitely keep that formula in my back pocket if I see it happen again! (It's strange because it is sporadic when it calculates the date difference wrong...and it's not an obvious catch

Do you happen to know if the gremlins appear only when you use an index/match to pull in a date from another sheet but not when you use a data mesh? If so I will switch to only using data mesh!

Thanks!

Paul Newcome

I honestly have never run into that particular problem myself. I think that it is a random bug and not necessarily related to any particular function or process.