0

I'm trying to calculate the number of days elapsed between dates using the IF function if the second date has not occurred yet to ignore the blank cell. My formula is working for the entire spreadsheet except for one row where I am getting #VALUE. There are no spaces in the date cells and both are formatted identical to the rest of the spreadsheet.

When viewing the calculation steps, one cell date looks like it is formatted as "general" instead of date, but it is 100% formatted as date. I tried changing the formatting for both cells and still get the #VALUE error.

3
  • "one cell date looks like it is formatted as "general" instead of date, but it is 100% formatted as date" ... what does that mean, exactly? What is your formula? What is the format of the cell containing the formula? – cybernetic.nomad Apr 24 '19 at 15:47
  • "one cell date looks like it is formatted as "general" instead of date, but it is 100% formatted as date"... what does that mean, exactly? What is your formula? What is the format of the cell containing the formula? - The formula is: =IF((AND(ISNUMBER(N108))), N108-G108, ""). The cell creating the formula is formatted as general. N and G are both formatted as date cells. – Brenda E Apr 24 '19 at 17:39
  • Please edit your question instead of adding information in comments (and no need to repeat my comment) A screen cap may help understand what you mean. you can upload one to imgur.com and link to it – cybernetic.nomad Apr 24 '19 at 18:04
1

Formatting of cells does not control the contents of cells, it just controls the fashion in which they are displayed. This is very important to note especially for dates. Formatting a cell as Date does not make the contents a date. The contents remain unchanged. Excel however does convert the contents to display in a format we are more accustom to seeing.

Excel stores dates as integers and times as decimal (or fraction of a day). If you enter 0.5 in a cell with general format, it will display as 0.5 However if you then change the format to Time is will display as 12:00 or what ever your local time format is set too. If you turn around and change the format back to general the display should be 0.5. Dates are stored as the number of days since 1900/1/1 with that date being 1. Again you can test this by entering 1 in a cell and then formatting it as date. If you are on a Mac I believe the year will be slightly different.

You state in your comment that your formula is:

=IF((AND(ISNUMBER(N108))), N108-G108, "")

This is great as you are testing the date in N108 to see if its a number or a string. If its a number you are performing your math operation on it and if it is not a number to return a blank. One thing you have not done is to test the value in G108 to see if it is a number or not as well.

On a side note, your AND in your formula is not currently serving any purpose as there is only 1 term inside the AND function. AND functions should be used when comparing 2 or more terms and either needing them to ALL be TRUE or At least one to be FALSE.

WITHOUT knowing what your sheet is doing I am guessing you want your formula to look something like:

=IF(AND(ISNUMBER(N108),ISNUMBER(G108)), N108-G108, "")

The above formula would test to see if both the value in N and G are numbers before trying to do the math operation. If either is not a number it will return blank.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.