Converting a date range to number of months or weeks

  • Thread starter Thread starter Phyllis
  • Start date Start date
P

Phyllis

I need to write a formula that will take the difference
between two dates and convert it weeks or months.

Example:
I have an employee that is eligable for 5 weeks vacation
as of 1/1/2004. I want to know what's he's accrued as of
2/29/04. Start date is 1/1/04 end date is 2/29/04, the
difference is 60 days (using a 360 day calendar). The
employee cannot use less than 1 week of vacation at a
time. So I want to know how many weeks has he accrued as
of 2/29. Some employees accrue vacation from their
anniversay date, so I need a formula that will work with
any date range.

Is this possible to do without having to write a macro or
something like that?
 
Hi Phyllis!

I'd go for weeks rather than months because weeks is a constant
whereas months is a variable. It could create ructions with
calculations of holiday entitlements.

For Weeks:
Where A1 is the start date and B1 is the end date.
=(B1-A1)/7

If you go for months:
=DATEDIF(A1,B1,"m")

But there are more than 52 weeks in a year, so you might be best
calculating on a daily apportionment basis.
--
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.
 
Thanks, that is so obvious, I can't believe I didn't think
of that. I kept thinking of weeks as 5 days (5 workdays)
and it just wasn't working out right. Thanks again.
 
Hi Phyllis!

Thanks for thanks is always appreciated and shows Google searchers
that solutions work:

Re: I kept thinking of weeks as 5 days (5 workdays) and it just wasn't
working out right.

You must only count those days with a y in the name.

--
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.
 
Back
Top