Solved How to formulate filtering items that are within 48hrs

May 26, 2015 at 12:01:25
Specs: Windows 8
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?


See More: How to formulate filtering items that are within 48hrs

Report •

✔ Best Answer
May 26, 2015 at 13:03:02
re: i can't code

There 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    OT

One 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



#1
May 26, 2015 at 12:27:17
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.


Report •

#2
May 26, 2015 at 12:31:17
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.

Report •

#3
May 26, 2015 at 12:40:15
date name task
5-20 xxx OT
5-25 xx OT

i 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.


Report •

Related Solutions

#4
May 26, 2015 at 13:03:02
✔ Best Answer
re: i can't code

There 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    OT

One 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


Report •

Ask Question