Calculating ages and dealing with blank cells

[halesowenmum]

Hi

Can someone help me with a formula for the following. I have to be able to show the age of some people who have been part of a pilot. The people completing the data collection sheets should have written the age in but helpfully haven't done that for some of the people. Where there is no age given there is however their date of birth. I'm wanting consistent age results in column D. DOB and Date of Contact are both date fields, age is currently a number field (you'll need to tell me if I need to change this).

	B	C	D	E	F
5	Name	DOB	Age	Sex	Date of contact
6	Harry Jones		75	Female	26/04/2017
7	Laura Smith	19/06/1942			04/05/2017

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

What I need to do is for all instances like C6 is blank allow me to type in the age myself in D6 and for instances like C7 where there is a date in C7 take the date of birth and using the date of contact display the age in D7.

Is that possible to do???

 

[gallen]

In Column D on row 7 you could use a formula like:

=INT(YEARFRAC(C7,F7,1))

 

[halesowenmum]

Yes that works fab in ro2 7 where there isn't an age in D - but ideally I need something I can fill down which will also do something useful along the lines of 'if there's nothing in c6 (for example) then in d6 leave it blank' - and would I still be able to put an age in manually in d6????

 

[Stiuart_W]

Can you be a bit clearer about what you want?

When you have in column D - "Age" you need to qualify that - Is that the age as of a specific fixed date, Say 1 Jan 2017, or is it the age today, when the workbook is opened?
You say that the Age value is typed in but see previous comment

But taking what you say on face value then:-

=IF(C7<>"",INT(YEARFRAC(C7,F7,1),"")

 

[gallen]

Just check within your formula if either cell is blank. Copy this into D6 and copy down. Change the text value where "Age Required" to anything that suits

=IF(OR(ISBLANK(C6),ISBLANK(F6)),"Age Required",INT(YEARFRAC(C7,F7,1)))

 

[halesowenmum]

Stiuart_W thank you so much for responding.

I'd hoped the table would show that where no DOB is provided the age is typed in manually and is formatted as number, where there is no age provided to me but there is a DOB then the DOB and today's date are used to populate the age in D. So age is the age of the person we're contacting.

I've tried the formula you suggest above (thank you) but it tells me there are too many arguments in the function.

 

[gaz_chops]

You could copy the formula & paste to the blank cells only.

 

[halesowenmum]

Hi gallen.

If we're looking at row 7 where we have a DOB and the date that patient was seen can that be set to return the person's age in D?

But also work for examples like row 6 where there is no DOB but there is an age - and in these cases it does what you've suggested and returns Age Required.

F6 will never be blank by the way, just C6 or C7 or C8 etc.

It only needs to return Age Required if C6, C7, C8 etc are blank - but it returns it even if there's a DOB in there. If there is a DOB in there it needs to use the DOB and the today's date to calculate the age of the person for col D.

I may have to lie down in a darkened room shortly - this is a tricky one!!

 

[Stiuart_W]

Sorry

Missed a close brackets

=IF(C7<>"",INT(YEARFRAC(C7,F7,1)),"")

 

[halesowenmum]

Yes I could do that I see what you're saying but I'm hoping for something where I'm not having in some way to skip rows - I'm hoping for something which can do it all within the one row dependent on what is in C..... that I can copy down at will.