Forum Discussion

Copper Contributor

Feb 13, 2020

Formula solving without data

Hello! I am trying to create a sheet that I can track how many days an item is in the sheet and then marked resolved when I place the date resolve. When copying the formula it gives me the co...

Formulas and Functions

Brass Contributor

Feb 13, 2020

I am not sure I understand your question, but 48873 is a date (Oct. 21, 2033) formatted as a number.

MelV19
Copper Contributor
Feb 13, 2020
cliff brockI don't want it to be considered date, but a number. I am trying to have the column B only have how many days its been on the Excel sheet considering the current date until I place a date on Column C which then will put it in as Resolved.

Hope this makes more sense.

Thank you!
- Riny_van_Eekelen
  Platinum Contributor
  Feb 13, 2020
  MelV19
  Your formula in B checks if C is empty. If so, then you take today's date minus the value in A, otherwise you write "Resolved". But when A is empty, the formula returns TODAY() - nothing = today's date. And since your cells in B are probably formatted as "General" (i.e. not dates) you get the value 43873 as in your picture. Look at the sheet today and it will show 43874. To avoid this, you need to build in a check to see if A is empty as well. Because when that is the case, you don't want to do a calculation at all. The formula below (in B2 and copied down) does that and is also makes sure that you don't get an ERROR in case A contains a text.
  =IFERROR(IF(A2="","-",IF(C2="",TODAY()-A2,"Resolved")),"-")
  If the error checking is not important for you, the formula will look like this (being close to what you had already figured out yourself):
  =IF(A2="","-",IF(C2="",TODAY()-A2,"Resolved"))

Resources