Trying to make a 'Days Since' cell, that checks three other cells for dates (or any text), and outputs a number based on those cells.
Hey Excel community! I have three dates on my sheet for each line: the date submitted, the date last actioned and a completion date if completed.
I am trying to have a 'Days Since' column that outputs the number of workdays since the Last Action date. If there is no Last Action I want it to output the number of workdays since the Submitted date. BUT, if there is a date (or text) in the Completed column, I want it to be empty.
I have gotten it to output 'days since' the Last Action date,
=IF(ISNUMBER([@[Last Action]]), MAX(0, NETWORKDAYS([@[Last Action]] +1, TODAY() )), "") without including the Last Action date itself (the +1), and not being negative for stuff entered today (the MAX(0,). But my attempt to work from the Submit date, if there is no Last Action date haven't been as successful (mostly formula errors) or continuing to accrue days on stuff that has been completed, which I would prefer just be blank.
I made some progress that I think is kind of doing what I want, but any line without a date returns a large number that it throwing off my conditional formatting, and I think it is just a messy way of doing what I want:
=IF(AND([@[Last Action]]="", [@Completed]=""), MAX(0, NETWORKDAYS([@Submitted] +1, TODAY() )), IF(ISNUMBER([@[Last Action]]), MAX(0, NETWORKDAYS([@[Last Action]] +1, TODAY() )), "")) I was wondering how to make it be blank if there are no dates, and also if there is just a better way to do this..?
[link] [comments]
Want to read more?
Check out the full article on the original site