Calculating elapsed time from Max day in row

**iamconstance** · 03-19-2013, 03:22 PM

Hi All,

First - thanks for your help.

I am having a hard time getting an answer to this question - probably because I'm not searching it properly.

So, hopefully I can explain it properly.

I am trying to calculate elapsed time (in days) between when one activity is performed (and logged by date in the sheet), to when another activity is performed. I will be using this data to demonstrate service standards.

So in columns AI AJ AK AL AM AN AO of each row, the user would enter a date.

In column BA, I want to calculate the difference between column AI and AJ. In Column BB, I want to calculate the difference between AJ and AK - and so on and so forth.

My big issue is that column AM and AN are dependant on a report provided by others - so this data doesn't always get populated as the process happens - and may not happen for a few weeks. Right now, my formula calculates it as 28975 days elapsed since there is no date.

Is there anyway to combine the max function, with the networkdays function to calculate the difference between the date in cell AO and the greatest date entered before it? Also - note that there should be an IF formula in there that says if the cell AFTER the last completed cell is empty - calculate the difference between the last cell and today's date.

Here is the formula I am using now (would be entered in cell AK)
=IF(AND(ISBLANK(AK4),ISBLANK(AL4)),"",IF(ISBLANK(AL4),NETWORKDAYS(AK4,$C$1,'Data Sheet'!$J$4:$J$42),NETWORKDAYS(AK4,AL4,'Data Sheet'!$J$4:$J$42)))
So, if there is no date in AK or AL - leave cell AK blank. If only AL is blank, calculate the networkdays between the date entered and today. If they both have something in them, calculate the networkdays between the two. But - if AK, AL and AM are blank, AN has a date, AO is blank and AP has a date - how do I calculate the difference between AP and AN?
Where C1 is today's date and the data sheet holds the holidays.
Can any of you smart folks help me?

**Pauleyb** · 03-20-2013, 09:27 AM

A couple of things are confusing me, and likely explain why there is no response to this thread, yet.

Also - note that there should be an IF formula in there that says if the cell AFTER the last completed cell is empty - calculate the difference between the last cell and today's date.

Where does this answer go? In the cell next to it? For example, if AK is the last date input, then you want AL to be TODAY()-AK? That's okay, but realize once you do that, then AL is no longer empty and any checks on its 'emptiness' will be false.

Here is the formula I am using now (would be entered in cell AK)
=IF(AND(ISBLANK(AK4),ISBLANK(AL4)),"",....

Is that a typo? You will get a circular reference warning if you try to base the value of a cell upon its original value. Did you mean column BB? Basically, you cannot have a formula in 'this_cell' which says something like:
=IF(this_cell=5, 5, 0)
When Excel does the check for 'this_cell' it cannot use its past calculation as input. If it could, you would get crazy things like:
=this_cell+1
which would cause excel to keep incrementing the value in the cell until an error occurred.

Finally,

column AM and AN are dependant on a report provided by others ... and may not happen for a few weeks

and then you say:

if AK, AL and AM are blank, AN has a date, AO is blank and AP has a date

So how does AP have a date but AM is blank? That is counter to your original statement where AM and AN may be the delayed inputs.

Honestly, this sounds like a mess with maybe no solution, or a problem where the rules will change with every offered solution.

I recommend you concisely detail your inputs and your expected outputs.

Calculating elapsed time from Max day in row

LinkBack

Thread Tools

Rate This Thread

Display

Calculating elapsed time from Max day in row

Re: Calculating elapsed time from Max day in row

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions