Array Formula Query

%3CLINGO-SUB%20id%3D%22lingo-sub-1548781%22%20slang%3D%22en-US%22%3EArray%20Formula%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548781%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20attached%20a%20very%20rudimentary%20copy%20of%20my%20agenda%20that%20i%20made%20in%20excel.%20I%20need%20to%20use%20the%20column%20labeled%20due%20date%20and%20the%20column%20labeled%20time%20until%20due.%20When%20I%20input%20a%20due%20date%20into%20the%20due%20date%20column%2C%20I%20want%20the%20time%20until%20due%20column%20to%20automatically%20calculate%20the%20number%20of%20days%20from%20today%20until%20the%20due%20date.%20I%20used%20to%20use%20an%20array%20formula%20in%20google%20sheets%20with%20a%20days%20and%20today%20formula.%20But%2C%20I%20do%20not%20know%20how%20to%20do%20that%20in%20excel.%20Can%20someone%20help%3F%20I%20want%20it%20to%20be%20automatic%20instead%20of%20adding%20a%20formula%20each%20time%20hence%20the%20array.%20Thank%20you!%20I%20appreciate%20any%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1548781%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

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!

1 Reply

@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.