Needing help with Simple TODAY formula and Blank cells

[Jackie11]

Hi,

I'd appreciate any help with this formula.

Firstly, I have a spreadsheet with 4 headings from A1 – D1. (Name, Role, Actions, Date Submitted).

I have added an additional column in E1 (after Date Submitted) which tracks how many days has passed since ‘Date Submitted’. Formula I have used is: =TODAY()-D2.

The only problem with this formula is that if there is no date in the “Date Submitted” column, the formula comes back with “41458” under a number formatted cell, not date format.

How can I make the formula return a blank cell if there is no date in the column but when dates are added later on it will still calculate the number of days?

Thanks.

[sathiyamoorthy]

Hi jackie11,

try this =datedif(today(),d2,"d")

[FDibbins]

Hi and welcome to the forum

Maybe something like this?

=if(D2="","",TODAY()-D2)

side note: If you only have a few (hundred?) of this formula it wont be a problem, but if you have 100 000 then it might be better to put the today() part in its own cell and then just reference it - today() is a volatile formula, which means it gets recalculated every time ANYTHING in the workbook changes. So if you have today() used tons and tons of times, it could slow your file down eventually

[Jackie11]

Hi Sathiyamoorthy,
It doesnt seem to work either - the formula you provided returns the formula "#NUM!"

[sathiyamoorthy]

HI JACKIE11,

SORRY

TRY THIS try this =datedif(D2,TODAY(),"d")

[Jackie11]

Hi FDibbins,

The formula you provided works perfectly and will be fine for this purpose. Thank You!!

Next question (could be a little more tricky): What would be the best type of Chart to use, which would show the how many days are tracking (30 days, 60 days... etc) next to each Person and their Role.

I would also like to have the ability to filter the chart data with the Actions column. The ‘Action’ column has information like shortlisted, interviewed and commenced.

Unfortunately, charts are not my area of expertise.

Thanks again everyone!

[oeldere]

Make a table (on another sheet) in which you will look up the values (30 - 60 - 90 - 120 - 150 days).

After that you can use in a new column the VLookup function.

After that you can use that information for your charts.