Excel 2010: round up

[FiDonk]

Hello

I am looking for a formula to round up a date of birth to the relevant age e.g. if the person is 37 and 7 months the round up and age column total would automatically calculate 38. If the person was 37 and 6 months, the calculation would be 37.

I have three columns, DOB, Age and then Age rounded up.

Does anyone have any ideas of how I may do this please? I am at basic level with excel use.

Many thanks in anticipation.

 

[FiDonk]

sorry it was this:

C3: = DATEDIF(A3,B3, "y") + (DATEDIF(A3,B3, "ym") > = 6)

 

[FiDonk]

I moved my information to row 2 to try this:

C2: =DATEDIF(A2,B2, "y") + (DATEDIF(A2,B2, "ym") > = 6)

same result

 

[FiDonk]

apologies for all the postings! but just to save you time, I checked the cell is general and also that the cell was unlocked

 

[Dave Patton]

Quote
I am looking for a formula to round up a date of birth to the relevant age e.g. if the person is 37 and 7 months the round up and age column total would automatically calculate 38. If the person was 37 and 6 months, the calculation would be 37.

Excel 2010
	A	B	C	D	E	F
1				Round Age > 6 Months	Effective date	16-Jul-17
2	16-Dec-79	17-Jul-17	37 Years 7 Months	38
3	17-Dec-79	17-Jul-17	37 Years 6 Months	37
9d

Cell Formulas
Range	Formula
C2	=DATEDIF(A2,$F$1,"y")&" Years "&DATEDIF(A2,$F$1,"ym")& " Months"
C3	=DATEDIF(A3,$F$1,"y")&" Years "&DATEDIF(A3,$F$1,"ym")& " Months"
D2	=DATEDIF(A2,$F$1,"y")+(DATEDIF(A2,$F$1,"ym")>6)
D3	=DATEDIF(A3,$F$1,"y")+(DATEDIF(A3,$F$1,"ym")>6)

Named Ranges
Name	Refers To	Cells
Mon	="0111111"

Ensure that calculation is set to automatic.
File Options Formulas Workbook Calculations Automatic

Please ignore the defined name; it is not relevant to this question.

 

[FiDonk]

Hi

How did you get the effective date in F1?

 

[Dave Patton]

I just entered the date in F1. If you want to use today's date, enter =Today().

You can use Today() in the formula or reference the effective date which can be in a cell such as F1.

Excel 2010
	A	B	C	D	E	F
1				Round Age > 6 Months	Effective date	16-Jul-17
2	16-Dec-79		37 Years 7 Months	38
3	17-Dec-79		37 Years 6 Months	37
4	17-Dec-79		37 Years 6 Months	37
9d

Cell Formulas
Range	Formula
C2	=DATEDIF(A2,$F$1,"y")&" Years "&DATEDIF(A2,$F$1,"ym")& " Months"
C3	=DATEDIF(A3,$F$1,"y")&" Years "&DATEDIF(A3,$F$1,"ym")& " Months"
C4	=DATEDIF(A4,TODAY(),"y")&" Years "&DATEDIF(A4,TODAY(),"ym")& " Months"
D2	=DATEDIF(A2,$F$1,"y")+(DATEDIF(A2,$F$1,"ym")>6)
D3	=DATEDIF(A3,$F$1,"y")+(DATEDIF(A3,$F$1,"ym")>6)
D4	=DATEDIF(A4,TODAY(),"y")+(DATEDIF(A4,TODAY(),"ym")>6)

Named Ranges
Name	Refers To	Cells
Mon	="0111111"

 

[shg]

Another way:

	A​	B​	C​	D​
1​	DoB​	As Of​	~ Age​	​
2​	15-Jan-55​	15-Jul-17​	63​	C2: =ROUND(DATEDIF(A2, B2, "m")/12, 0)
3​	16-Jan-55​	15-Jul-17​	62​

 

[FiDonk]

Thank you so much to everyone for all of their assistance, I cracked it Dave Patton, thank you