Extracting data based on date

[mattyn]

Hi All

Firstly I hope everyone is staying safe and well.

Todays problem - straightforward spreadsheet that identifies a student accommodation planner. A number of worksheets for a number of accommodation venues.

Each worksheet consists of Columns A to L with specific info about the particular accommodation and its resident.

Col M onwards is the date planner - with each column as one day - and Row 9 has the date - so M9 is 1 Jun 20, N9 is 2 Jun 20 etc. The student and accom details are from row 10 onwards, and the accommodation team populate columns m onwards with a R if the student is resident in the block. I use =countif(M10:M500,"R")

Col M6 onwards count how many residents are in the accommodation for that day.

I want a Headlines Front Page that tells me the total # of residents in each venue today, in 7, 14, 21 days time - as a forecast.

So as columns on the headlines page - I need under the following headers:

Accom Venue	Total capacity	Resident Today	Resident + 7 days	Resident +14 days	Resident +21 Days
Text	Text	to be pulled from the ind worksheet count for today	pulled from the ind worksheet count for today + 7	pulled from the ind worksheet count for today +14	pulled from the ind worksheet count for today +21

Have no idea if this is possible - would appreciate some guidance please. Just hope this makes sense.

Very many thanks and kindest regards

matt

 

[MUKESHY12390]

here is the formula to highlight Last Name if there is any birthday for that record.

=AND($F2>=TODAY(),$F2<=TODAY()+29)

Here are the steps

GoTo --> Conditional Formatting --> New Rules---> Select "User a Formula to determine which cells to format"--> Paste this formula---> Select format ---> Click to OK--->


once you are done Goto --> Manage Rules --->Change Applies to B column

 

[MUKESHY12390]

here is the formula to highlight Last Name if there is any birthday for that record.

=AND($F2>=TODAY(),$F2<=TODAY()+29)

Here are the steps

GoTo --> Conditional Formatting --> New Rules---> Select "User a Formula to determine which cells to format"--> Paste this formula---> Select format ---> Click to OK--->


once you are done Goto --> Manage Rules --->Change Applies to B column


View attachment 16892

And the formula to calculate birthday's count.


=COUNTIFS(F:F,">=" & TODAY(),F:F,"<=" & TODAY()+29)

 

[mattyn]

Thank you again but....

I would like to base this on the Date of Birth, not the date of the birthday.

The formula above works when I use a date in col F of for example 28-Jun-20 but not when I use a Date of Birth such as 28-Jun-69.

Also, can we adjust to work for the next 30 days, rather than just this month - as it seems to with the above.

Much appreciated - if it is not possible please say. This is simply a nicety!!

Rgds

Matt

 

[MUKESHY12390]

use this...


=AND(DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))>=TODAY(),DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))<=TODAY()+29)

 

[MUKESHY12390]

pi

use this...


=AND(DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))>=TODAY(),DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))<=TODAY()+29)

people born in leap year are not included...

 

[MUKESHY12390]

people born in leap year dated 29 feb are not included... you want to include them as well.?

pi


people born in leap year are not included...

 

[MUKESHY12390]

finally....

=IF(NOT(AND(MONTH(F1)=2,DAY(F1)=29)),AND(DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))>=TODAY(),DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))<=TODAY()+29),IF(MONTH(DATE(YEAR(TODAY()),2,29))=2,AND(DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))>=TODAY(),DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))<=TODAY()+29),FALSE)) = True

Please check and confirm.

 

[mattyn]

pi


people born in leap year are not included...

So thank you again. I owe you a few pints for this help!

I used the formula above and have got the data to highlight if the birthday is due in the next 30 days. All good and not too fussed about 29 Feb. Great and thank you.

finally....

=IF(NOT(AND(MONTH(F1)=2,DAY(F1)=29)),AND(DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))>=TODAY(),DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))<=TODAY()+29),IF(MONTH(DATE(YEAR(TODAY()),2,29))=2,AND(DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))>=TODAY(),DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))<=TODAY()+29),FALSE)) = True

Please check and confirm.

Don't quite understand this bit - is this supposed to count - I don't see the Count function and all I get is TRUE. I tried adding this as the Conditional Format formula but a cannot include the last bit - namely Y()+29),FALSE)) = True

VMT

 

[MUKESHY12390]

=IF(NOT(AND(MONTH(F1)=2,DAY(F1)=29)),AND(DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))>=TODAY(),DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))<=TODAY()+29),IF(MONTH(DATE(YEAR(TODAY()),2,29))=2,AND(DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))>=TODAY(),DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))<=TODAY()+29),FALSE))=TRUE

this is for both problem... if you put this formula in Conditional formatting it will highlight upcoming birthday for you.

And

if you put in one cell and drag to the last record and in next column you can use CountIf formula to count "True" value for total birthday count.