Time between 2 dates with 2 conditions

**13sydwest** · 08-20-2011, 11:37 PM

I need help with formula to calculate time (in weeks) from date of intake to date of allocation for those aged 4y 0m or older but younger than 18y 0m (at intake) who are triaged as urgent (as in attachment with irrelevant cells hidden).

Formula used to get age at intake was of form : =DATEDIF(D3,B3,"Y") & "y " & DATEDIF(D3,B3,"YM") & "m"

Any help appreciated

**ConneXionLost** · 08-21-2011, 12:19 AM

How about:

=IF(AND(DATEDIF(D3,B3,"Y")>=4,DATEDIF(D3,B3,"Y")<18,G3=1,ISNUMBER(H3)),ROUND((H3-B3)/7,0),"")

Cheers,

**13sydwest** · 08-21-2011, 02:59 AM

Thanks. That's perfect. However, I thought I'd be able to work out 2 more functions from here but had no luck so would appreciate further help on them please:
1. Exactly the same formula again but the data are in a different worksheet called "Data"
2. A formula that will give the no. of those aged 4y 0m or older but younger than 18y 0m (at intake) who are triaged as urgent and allocated within 1 week of date of intake
Thank you

**ConneXionLost** · 08-21-2011, 04:25 PM

How about attaching a sample spreadsheet? (Word docs with pictures don't really help with formula construction.)

**13sydwest** · 08-22-2011, 07:33 AM

Thanks for the advice.

Relevant file attached. I've left everything in the summary pages as otherwise there'd be chaos there.

Data is in the "Bankstown" worksheet.

The 2 formulae I'm lost on are in highlighted cells:

(1) cell G11 of worksheet "Summaries": Time (in weeks) from date of intake to date of allocation for those aged 4y 0m or older but younger than 18y 0m (at intake) who are triaged as urgent.

(2) cell C8 of worksheet "Clinical indicators 4-6": No. of those aged 4y 0m or older but younger than 18y 0m (at intake) who are triaged as urgent and allocated within 1 week of date of intake.

With these I should be able to work out the rest.

Thank you