Forum Discussion

nlangbo's avatar
nlangbo
Copper Contributor
Jul 27, 2020

Array Formula Query

Hello, I have attached a very rudimentary copy of my agenda that i made in excel. I need to use the column labeled due date and the column labeled time until due. When I input a due date into the due date column, I want the time until due column to automatically calculate the number of days from today until the due date. I used to use an array formula in google sheets with a days and today formula. But, I do not know how to do that in excel. Can someone help? I want it to be automatic instead of adding a formula each time hence the array. Thank you! I appreciate any help!

excel
Excel for web
Formulas and Functions

1 Reply

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 27, 2020

    nlangbo   Here is a formula you can use:

    =IF(B3<>"",MAX(INT(B3-NOW()),0),"")

    Paste in K3 and drag/copy down as far as you want.  If this was in an Excel Table then it would autofill as you added additional rows.

    I included the INT() to make it Days and I added the MAX() to make things that have already passed show as 0 instead of negative numbers, but you want it to show negative time.  The IF(B3<>""... part is to show blank if no Due Date is entered. 

Resources