Hi Becky!
Always pleased to explain formulas; we're not just into providing
solutions.
Re:
=IF(D1="weekly",INT((B1-A1)/7),DATEDIF(A1,B1,LEFT(D1,1)))
DATEDIF has three arguments.
Start date
End date
Unit
The units allowed include d for days, m for months and y for years.
But there isn't a w for weeks option.
In D1 you have one of the four entries daily, weekly, monthly, or
yearly
Weekly, I handle as the first case. The rest I handle using DATEDIF
and extracting the first letter of the word in D1 which happen to
represent the right unit letters for the period.
If you have a whole series of these to work out, you will need to use
careful absolute and relative cell referencing. So if you have 2
columns of pairs of dates and you want the differences using a
constant period in D1 you'd use:
=IF($D$1="weekly",INT((B1-A1)/7),DATEDIF(A1,B1,LEFT($D$1,1)))
If you want user flexibility, you might use a drop down in D1.
Data > Validation > List [provide a range with the four options or
type them in separated by commas].
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
