Date Formula Question

[stormie_49548]

I have a spreadsheet of families that tracks visits for the month. Each family has an authorization date that ends in 6 months. New families could be added to the sheet during the month, and the sheet starts fresh every month.

I am trying to accurately calculate the number of required visits for that month (which is 3 per family 1 per week). I can make the sheet calculate the number of visits but the number becomes inaccurate if I add a family in the middle of the month where there are only 2 chances for a visit.

Any ideas how to accomplish this?



Many Thanks

Stormie

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

What you ask sounds possible but we would need to have a bit more detail about the rules for defining how many more visits are possible in a month.

For instance, does the number or possible remaining visits depend only on the number of days remaining in that calendar month? So, could we say if there are 14 days left there could be 2 visits but if there are only 13 days left then only 1 will be possible.

Is this affected by calendar weeks or when the weekends occur?

 

[stormie_49548]

Thank you for your reply.

The number is based on a 5 day work week. So even if there are 14 days there is chance for two visits. It would be ok to rate it by how many weeks left in the month.

Thanks