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: 280
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, 6 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,423
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

Try the following applied to C2:C10



=AND($A1=$A2,$B1=$B2,DATEDIF($C1,$C2,"m")<9)
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: 280
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,423
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

You're welcome
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 - Senior Forums

All times are GMT -7. The time now is 10:43 AM.


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