Excel - question on formula for dates Hi there, I\'m trying to put together a wo

Question

Excel - question on formula for dates

Hi there,

I'm trying to put together a worksheet of personnel available for projects. I've created below a table that shows a section of my excel worksheet to try and help explain my question. Assume that today's date is 1 Oct 2014.

My worksheet includes a column of dates (column D) which indicates for each person when they're available to start work. It also includes a column that shows when they become unavailable again (column E - note that some people have open-ended availability and not a specific date). I'd like to be able to filter my worksheet to see who is available for work in the next 3 months (column B) or the next 6 months (column C). Filtering by date (at least in the format I have it) is a little clumsy. I'd really like to be able to filter on the content of column B or C as I've included below. People will either be available for the full 3 or 6 months (yes), not available for the full 3 or 6 months (No) or available for some of these periods (Partially).

My questions are:

1. Is there a formula that will look at the date range in ccolumns D and E foor a given person, take into account the actual date, and enteer in columns B and C the appropriate availability response as 'Yes' oor 'No' or 'Partially'?

2. Is there a smarter way to do this?

A B C D E 1 Name Available in next 3 months?? Available in next 6 months?? Date available to start worrk End date available to work 2 Bob Yes Yes 01-Oct-14 Open 3 Susie Yes Partially 05-Dec-14 23-Feb-2015 4 Amir No No 03-Jul-15 Open 5 Dana Partially Yes 04-Dec-14 01-Jul-2015

Explanation / Answer

Hello ,,,

Try this for 3 months:

=IF(AND(TODAY()>=D3,IF(E3="Open",TODAY()+90,E3)>=TODAY()+90),"Yes",IF(TODAY()+90<D3,"No",IF(OR(TODAY()<D3,TODAY()+90>E3),"Partially")))

6 months:

=IF(AND(TODAY()>=$D2,IF($E2="Open",TODAY()+180,$E2)>=TODAY()+180),"Yes",IF(TODAY()+180<$D2,"No",IF(OR(TODAY()<$D2,TODAY()+180>$E2),"Partially")))

There is a still a small issue. Today() is today so the formulas will return partially:

If we could say, we want the availability as of the first of next month, we can write the formulas as:

(for 3 months)

=IF(AND(EOMONTH(TODAY(),0)+1>=$D2,IF($E2="Open",EOMONTH(TODAY(),0)+1+90,$E2)>=EOMONTH(TODAY(),0)+1+90),"Yes",IF(EOMONTH(TODAY(),0)+1+90<$D2,"No",IF(OR(EOMONTH(EOMONTH(TODAY(),0)+1,0)+1<$D2,EOMONTH(TODAY(),0)+1+90>$E2),"Partially")))

Name Available in next 3 months? Available in next 6 moonths? Date available to start work End date available to work Bob Yes Yes 01-Oct-14 Open Susie Partially Partially 05-Dec-14 23-Feb-15 Amir No No 03-Jul-15 Open Dana Partially Yes 04-Dec-14 01-Jul-15 Partiallyy Partially with formulas Partially Partially No No Partially Partially

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.