I have a spreadsheet with several columns and one of them is for date (column D). How do I make the next column (E) automatically display the days passed from date in D until now on open?

I have tried with


but it displays #VALUE!

1 Answer 1


Use the formula =IF([cell on same row in D column] <> "", today()-[cell on same row in D column], "")

So in D2 (for example):

=IF(D2 <> "", TODAY()-D2, "")

TODAY() returns the current date. The current date minus the specified date equals the days passed between the dates.

The code above means "If D2 is not empty set current cells value to days since else set it to be empty"

  • I think this is the right answer but I have 2 issues... I'm used to read the date like dd/mm/Y (23/04/2014) - is it possible to make google spread sheet to treat this format as date ? And how to apply this rule to every row, because now is applied only for E4 ?
    – T1000
    Apr 23, 2014 at 20:48
  • 2
    To apply it to every row, enter it in the first row and with the cell selected grab the little blue square in the bottom right corner and drag down to copy to all other cells. Apr 23, 2014 at 20:50
  • Thanks, but is it possible to not display nothing if col D is empty because now E is displaying current date when D is empty ? And can you help me with format issue pls ?
    – T1000
    Apr 23, 2014 at 20:56
  • 2
    To change Date format, select the date column, go to menu option Format>Number>More Formats>More date and time formats. Clear the textbox that appears and - using the little arrow at the right side of the textbox - enter desired format. Apr 23, 2014 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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