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???

 

[halesowenmum]

Yes it works now thank you.

However it still only deals with one thing - how to return the age of the person if there's a DOB - is it possible to weave in something that will also address (on the same row) what should be done if there is no DOB - kind of an amalgamation of your formula and gallens, possibly, maybe, I don't know???

 

[gaz_chops]

The only way to achieve what you want is with vba or 2 helper columns.

 

[halesowenmum]

Ok, I'm game to try VBA having just successfully managed to do a data validation list that allows multiple entries in the same cell (go me and thanks to Contextures for their helpful video!!).

What would I need to do???

 

[jorismoerings]

Requesting and returning input through formula's is easier done via VBA.

try this:

Sub Filled_Age_Fields()

Dim Counter As Integer

'Optimize Macro Speed
    Application.ScreenUpdating = False
    Application.EnableEvents = False

'Determine how many rows sheet has and use it as Stop-Counter in For-Next Loop
Counter = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row - 5           'rows count in Source Sheet

'Determine for Each row is data field are filled and calculate Age
    For i = 1 To Counter
        If IsEmpty(Cells(i + 5, 4)) _
        Then
            If Not (IsEmpty(Cells(i + 5, 3))) And Not (IsEmpty(Cells(i + 5, 6))) _
            Then
               Cells(i + 5, 4).Formula = DateDiff("YYYY", Cells(i + 5, 3), Cells(i + 5, 6))
            Else
            Cells(i + 5, 4).Value = InputBox("Row " & i + 5 & " - Give me Age of " & Cells(i + 5, 2))
            End If
        End If
        
    Next i
    'Reset Options
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

 

[halesowenmum]

Cripes, I've fallen at the first hurdle!

I've already for that sheet got one page (?) of VBA for the multiple item list thingy - do I create a new one and if so, does it need to be General, Worksheet, Change or something else?

 

[jorismoerings]

Depends on what you already have...
Easiest one is general, if you already have a general one, just copy the code underneath the multiple item thingy
Excel will automatically created a divider.

In other case, just create a new general module.

 

[halesowenmum]

Brill, sorted. Thanks very much to one and all.