Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Range
I'm setting up a spreadsheet that includes arrival and departure dates for
about 500 people. I'm trying to set it up so that I can figure out who will be present on a given day. For example All people who will be present on March 5. That range would include people arriving January 15 and departing on March 6, but also people arriving March 5 and departing on April 10. Any suggestions on setting up a function and sort command combination that would allow me to do this. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Range
Hi
With Arrivals in column A, Departures in column B. In cell C2 enter your required Date e.g. 06/03/2006 In cell D2 =SUMPRODUCT(--($A$2:$A$100<=$C$2),--($B$2:$B$100=$C$2)) Note that this includes the = 06/03/2006 in both parts, as people leaving or arriving on that day will be present for some part of it. Adjust the formula as required if you don't want this. -- Regards Roger Govier "KevMo" wrote in message ... I'm setting up a spreadsheet that includes arrival and departure dates for about 500 people. I'm trying to set it up so that I can figure out who will be present on a given day. For example All people who will be present on March 5. That range would include people arriving January 15 and departing on March 6, but also people arriving March 5 and departing on April 10. Any suggestions on setting up a function and sort command combination that would allow me to do this. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Crate group of date, with Dynamic Range in pivot table not working | Excel Discussion (Misc queries) | |||
Date Range within one cell | New Users to Excel | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Date Range and calculation | Excel Worksheet Functions | |||
Update cell based on date range | Excel Discussion (Misc queries) |