Excel formula depend on date

%3CLINGO-SUB%20id%3D%22lingo-sub-2213438%22%20slang%3D%22en-US%22%3EExcel%20formula%20depend%20on%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2213438%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20an%20Excel%20file%20stored%20in%20MS%20Teams%20for%20updating%20by%20others.%20If%20they%20don't%20key%20in%20actual%20results%20by%20a%20date%20and%20timestamp%20say%2016%20March%2C%2012%20midnight%2C%20the%20calculation%20should%20show%200%2C%20otherwise%20the%20formula%20using%20the%20keyyed%20in%20data%20will%20calculate.%20How%20do%20you%20formulised%20such%20a%20calculation%20in%20Excel%20that%20dependent%20on%20dateand%20timestamp%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2213438%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2213467%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20depend%20on%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2213467%22%20slang%3D%22en-US%22%3EThank%20you%20for%20connecting%20to%20Microsoft%20Community%2C%20Level%205%20Experts%20are%20going%20to%20help%20you%20to%20fix%20your%20problem.%20Please%20Connect%20to%20our%20support%20no%20%CB%A6-8.8.8-%E1%97%B1.%F0%90%90%9E.%F0%90%90%9E-O.2.2.2%3C%2FLINGO-BODY%3E
Contributor

I am an Excel file stored in MS Teams for updating by others. If they don't key in actual results by 15th day of every month, 12 midnight, the calculation should show 0, otherwise the formula using the keyyed in data will calculate. How do you formulised such a calculation in Excel that dependent on present date and timestamp?  

1 Reply

Hi @Chan_Tze_Leong 

You can achieve this by using Now() function but it will provide desired result only when Calculation Option is set as "Manual". 

tauqeeracma_1-1615920397831.png

 

As mentioned in below image, whenever any data is entered in coloumn C, date & time stamp is updated in column D but again it will work only with manual calculation because now() functions gives the latest date & time and if calculation option is turned as "Automatic" for each row it will show the same latest date & time.

Note: Please use Table option to store data rather than regular cells so that your formula will be automatically for each new row added.

tauqeeracma_0-1615920318752.png

Hope it will help

 

Thanks

Tauqeer