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:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

DoB​

[/td][td="bgcolor:#F3F3F3"]

As Of​

[/td][td="bgcolor:#F3F3F3"]

~ Age​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

15-Jan-55​

[/td][td]

15-Jul-17​

[/td][td="bgcolor:#E5E5E5"]

63​

[/td][td]C2: =ROUND(DATEDIF(A2, B2, "m")/12, 0)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

16-Jan-55​

[/td][td]

15-Jul-17​

[/td][td="bgcolor:#E5E5E5"]

62​

[/td][td][/td][/tr]
[/table]

 

[FiDonk]

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