I cant get my formula to add a day for the 8th of every month

[Shellybelly]

Anyone knows a formula that will add one day for a specific date of every month?

[Tony Valko]

Not enough detail.

Are you wanting to count how many 8th of the months have passed since a start date?

[Shellybelly]

I have a vacation tracker that I've built and I want the total vacation days acquired show how many days an employee acquired every 8th of each month. So if an employee was hired on the 8th of august 2014 then on the 8th of September 2014 the employee should acquire 1 vacation day. is there a formula that can do that. I'm trying alot of if statements out of my head but can't seem to get it to work.

[Yogi52o]

What if an employee was hired on the 7th of august; do they get a day on August 8?

[Tony Valko]

Maybe this...

A2 = date of hire

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A2&":"&TODAY())))=8))

[Shellybelly]

No if they were hired August 7th 2014 then they would Acquire a day on September 7th 2014. so basically they will acquire a vacation day on every hired day of each month, which would be the 7th of each month.

[Yogi52o]

Please Login or Register  to view this content.

This takes the difference between the startdate and the checkdate (either TODAY() or some pre-set date you want to check against), expressed in months/"m"

[Tony Valko]

Hmmm...

What if their hire date was on a leap day?

[Shellybelly]

Thanks Tony but the formula gives me 72 days acquired. basically i need it to add a day, every hired date for the employee for each month...so for an example. the employee's hired date was 8-sept-2013 so by 8-oct-2014 the employee will acquire 1 vacation day, if the employee didn't take any vac day then by 8-Nov-2014 the employee will acquire another vacation day so in total the employee will have 2 vacation days. I need a formula that will continiously keep adding one day for every 8th(hired date) of each month.

So Every month the employee receives 1 to 1.5 day (from the 8-sept 2014 to 8-october-2014 = 1 month)

[Shellybelly]

hmmm that, i didn't think off lol

[Tony Valko]

I need a formula that will continiously keep adding one day for every 8th(hired date) of each month.

That's what my formula does.

However, there's a problem with leap years. I can't think of a way to modify the formula to account for leap years/days.

[gak67]

Tony, I don't think your formula would work properly for any start date greater than the 28th of any month - not only for leap years, but also if they started on the 31st it would miss 5 months each year.

[Yogi52o]

Did you try the =DATEDIF(StartDate,CheckDate,"m") ? That will give you the number of total elapsed months, which you can multiply by 1 or 1.5 as needed.

[Tony Valko]

Yeah, I didn't think this one through very well!

Depending on how they want to handle leap days, the DATEDIF formula should do what you want.

=DATEDIF(start_date,NOW(),"m")

[Shellybelly]

It Works guys!!!!! thanks sooo much.

[Tony Valko]

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.