Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-20-2020, 07:34 PM
Marcia's Avatar
Marcia Marcia is offline Format if the age gap is less than 9 months Windows 7 32bit Format if the age gap is less than 9 months Office 2013
Expert
Format if the age gap is less than 9 months
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Format if the age gap is less than 9 months

Hi. The sheet has 3 fields (Family No, Member and Bdate). In the birthdate, I pasted the ff formatting formula:
=AND($a$2:$a$10,$a2,$b$2:$b$10="C",$C3-$C2<9)
where: Col A = Family No, Col B = Member, Col C = Bdate)


The birthdate of "C"s with less than 9 months gap from the other "C"s within the family should be formatted. Kindly point out what is missing in the formula.
Thank you.
Attached Files
File Type: xlsx AgeGap.xlsx (9.8 KB, 10 views)
Reply With Quote
  #2  
Old 05-21-2020, 12:33 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Format if the age gap is less than 9 months Windows 7 64bit Format if the age gap is less than 9 months Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Try the following applied to C2:C10



=AND($A1=$A2,$B1=$B2,DATEDIF($C1,$C2,"m")<9)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 05-21-2020, 01:06 AM
Marcia's Avatar
Marcia Marcia is offline Format if the age gap is less than 9 months Windows 7 32bit Format if the age gap is less than 9 months Office 2013
Expert
Format if the age gap is less than 9 months
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

=AND($A1=$A2,$B1=$B2,DATEDIF($C1,$C2,"m")<9)[/QUOTE]
Thank you! Pecoflyer. I changed the $B1=$B2 to $B1="C"
This formula validates birthdates of siblings. We were told that parents and children's bdates in the database should not be less that 8 yrs difference. Who gives birth at 8 yrs old? Horror!!
Reply With Quote
  #4  
Old 05-21-2020, 07:52 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Format if the age gap is less than 9 months Windows 7 64bit Format if the age gap is less than 9 months Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

You're welcome
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Format if the age gap is less than 9 months Adding Months TimmiAndrew Excel 2 09-16-2015 10:21 AM
Format if the age gap is less than 9 months Is it possible to format today's date minus 12 months sarabeck Word 1 07-09-2015 04:53 PM
Format if the age gap is less than 9 months entering duration in months ketanco Project 1 12-17-2014 01:09 PM
Format if the age gap is less than 9 months chart with months windseaker Excel 1 04-29-2011 11:45 PM
Setting up recurring months to skip some months etc. dwelch@ykfireprevention.c Outlook 0 11-30-2010 10:15 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:11 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft