So our company has a spreadsheet with different pending task or completed task. we are trying to formulate an equation that can show a number at the end of page saying how much pending that falls outside of the 48 hours frame. for example, task done on 1 april, and still pending on 4 april then that will be count toward pending outside of 48hrs frame. Is there a formula to do so?

✔ Best Answer

re: i can't codeThere is no coding required to set up columns for example data. Simply click on the pre tags found at the top of the Reply box and enter your data in between those tags:

A B C 1 date name task 2 5-20 xxx OT 3 5-25 xx OTOne solution is to enter this formula in D2 and drag it down:

=IF(TODAY()-A2>=2,1,"")

Whenever the date in Column A is 2 or more days (48 hours) less than Today's date, a 1 will be placed in Column D. If it is less than 2 days, Column D will remain blank.

Then all you have to do is SUM Column D.

You could also use a letter or word and the COUNTIF function:

In D2, use this formula:

=IF(TODAY()-A2>=2,"Late","")

then:

=COUNTIF(D1:D100,"Late")

If you want, you could put those formulas in a hidden column and just display the total. There are lots of options.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Your question is a little confusing. Your example says " task done on 1 april, and still pending on 4 april" How can it be "done" on April 1 yet still be "pending" on April 4th?It would help if you gave us an idea of how your spreadsheet is laid out. For example, do you have Expected Completion Dates in a column and perhaps another column that denotes each task as Pending and/or Completed, etc? That's the type of information that would help us help you.

Please click on the following line and read the instructions on how to post example data in this forum. Then post a short example of how your data is laid out. Be sure to include column letters and row numbers as shown in the example.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

we need to wait for a response from our counterpart therefore it is called pending . If it's easier ... you can see it as uploaded on 1st , and we need to get a reply within 48 hours time frame, so if we haven't gotten reply back by the 4th we call it pending.

date name task

5-20 xxx OT

5-25 xx OTi can't code... so there is a small sample that i can provide. so we want that everyday when we open excel, we can see whats outstanding as of today.

in this example, as of today (5-26) , the 5-25 is still in 48 hrs, but 5-20 will be outstanding. and we would like to have a formula to calculate how many of these outstanding in a number.

re: i can't codeThere is no coding required to set up columns for example data. Simply click on the pre tags found at the top of the Reply box and enter your data in between those tags:

A B C 1 date name task 2 5-20 xxx OT 3 5-25 xx OTOne solution is to enter this formula in D2 and drag it down:

=IF(TODAY()-A2>=2,1,"")

Whenever the date in Column A is 2 or more days (48 hours) less than Today's date, a 1 will be placed in Column D. If it is less than 2 days, Column D will remain blank.

Then all you have to do is SUM Column D.

You could also use a letter or word and the COUNTIF function:

In D2, use this formula:

=IF(TODAY()-A2>=2,"Late","")

then:

=COUNTIF(D1:D100,"Late")

If you want, you could put those formulas in a hidden column and just display the total. There are lots of options.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History