Extracting data based on date

mattyn

Board Regular
Joined
Apr 20, 2015
Messages
148
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 VenueTotal capacityResident TodayResident + 7 daysResident +14 daysResident +21 Days
TextTextto be pulled from the ind worksheet count for todaypulled from the ind worksheet count for today + 7pulled from the ind worksheet count for today +14pulled 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
 
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


Capture.PNG
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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)
 
Upvote 0
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
 
Upvote 0
use this...


=AND(DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))>=TODAY(),DATE(YEAR(TODAY()),MONTH(F1),DAY(F1))<=TODAY()+29)
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
VBA Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top