Date of birth and pop-up formula/VBA help in Excel

[ryan-cook]

Hi guys,

I need to create a formula in Excel, but I don't know where to start.

The spreadsheet will be used by bars to enter their staff details in. One of the fields will be the date of birth.

If the date of birth entered means that they are not yet 18, I need a pop-up to say: "Bar staff must be 18 years or over"

I don't know how to a) make a formula to tell me if the DOB makes them over 18 (do I need to update this formula every time we use it on a different date?), or b) how to create a pop-up.

Any help would be massively appreciated.

Thanks,
Ryan

[FDibbins]

"Hi and welcome to the forum

We would love to help you with your question, but 1st, in accordance with forum rules, please rename your thread to something more meaningful, that actually describes your problem.

Because thread titles are used in searching the forum it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title used as search terms would return relevant results.

Many members will look at a thread title, and if it is of interest to them, or it falls within their area of expertise, they might only open those threads.

Look at it this way...if you typed that title into google, what would you expect to get back?
To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title"

[ryan-cook]

No problem.

Thanks.

[TMS]

If you had today's date, =TODAY(), in cell A1 and the birth date in, say, cell B1, the following formula would give you the number of days:

=$A$1-B1.

So, =($A$1-B1)/365 would give you the number of years, give or take ... because it's not accounting for Leap Years.

Or, you could use: =DATEDIF(B1,$A$1,"Y")


Use Conditional Formatting to highlight dates that are less than 18.


Regards, TMS

[ryan-cook]

Thanks, TMS.

Instead of Conditional Formatting, is there a way that I can get a pop-up to appear?

[FDibbins]

Thanks

This wont flag you with a message, but it will highlight the cell with the color you choose. Assume your DoB is in A1 (adjust as needed)
1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =(TODAY()-A1)/365.25<=18 format fill RED

[TMS]

is there a way that I can get a pop-up to appear

AFAIK only with VBA.

Regards, TMS

[ragulduy]

I'm not sure how it works in 2010 but in 2003 you can could get the pop up box if you use data validation. The steps are like this, maybe someone can translate this into the 2010 version:
-select the cell where the date of birth will be entered, in this example i will use A1
-click data -> validation
-Under allow, Select custom
-In the formula box enter =IF((TODAY()-A1)/365.25>18,TRUE,FALSE)
-click the error alert tab
-enter the message you want to appear, e.g. "bar staff must be over 18"

[ryan-cook]

Thanks, guys.

With a combination of the above, I think I'm almost there.

I've got a =TODAY( ) formula in cell A1 on the spreadsheet, but I now need a formula aligned with this so that I can work out if someone is 18 or not.

So, I just need something along the lines of... IF A1 IS "THIS" DATE, THEN THE DATE OF BIRTH ENTERED MEANS THAT THIS PERSON IS/ISN'T 18

Hope that makes some kind of sense...

[ryan-cook]

Thanks, guys.

With a combination of the above, I think I'm almost there.

I've got a =TODAY( ) formula in cell A1 on the spreadsheet, but I now need a formula aligned with this so that I can work out if someone is 18 or not.

So, I just need something along the lines of... IF A1 IS "THIS" DATE, THEN THE DATE OF BIRTH ENTERED MEANS THAT THIS PERSON IS/ISN'T 18

Hope that makes some kind of sense...

Given that it is 2013, I guess I could just minus 5 years from the current date, which would work fine (until 2014). However, I'm not sure how I minus days/months/years in a formula.

[DBY]

Hello
If your DOB is in say cell B1 the following formula should flag anyone under 18:

Please Login or Register  to view this content.

You could also hard code the Today() function:

Please Login or Register  to view this content.

Here there's no need to reference cell A1.

Hope this helps.
DBY