susannahva
New Member
- Joined
- Jun 3, 2014
- Messages
- 1
I am working on a spreadsheet that will be easy for someone not that familiar with Excel to populate with data on visitors to our building. I am trying as much as possible to have a few fields where they input basic data and then let automatic calculations take it from there.
We offer classes that run for usually 6- to 8-week periods. As such, they will start in one month and finish in another. I need to come up with a way that someone can input the start & end dates of a class & the spreadsheet automatically determines how many classes were held in each month while the class was in session. So, if a class ran from June 3, 2014 to July 1, 2014, the spreadsheet could tell me the class met 5 times; 4 in June and 1 in July.
I have found formulas that will tell me how many times a day of the week occurs in a given month, and formulas that will allocate the days across a date range to the months they fall in, but I need something that will put that together & say--within this date range, how many Tuesdays are in June and how many are in July?
I know that Excel can automatically look at a date & tell what day of the week it is... However, one complicating factor is that some classes will meet multiple times a week--is there some way to take that into consideration?
Another complicating factor is that if a class meeting would fall on a holiday, it would need to be taken into consideration. (So a class running from June 4, 2014 - July 29, 2014--assuming we closed the week of July 4--would meet 8 times, not 9. I would need the calculation to be able to factor that in.)
I am trying to keep it all as simple as possible, the fewer moving parts the better since data entry will be done by someone who won't be able to trace the source of an error. However, at this point, I would be happy for ANY solution!!!
Thank you so much for your help!
We offer classes that run for usually 6- to 8-week periods. As such, they will start in one month and finish in another. I need to come up with a way that someone can input the start & end dates of a class & the spreadsheet automatically determines how many classes were held in each month while the class was in session. So, if a class ran from June 3, 2014 to July 1, 2014, the spreadsheet could tell me the class met 5 times; 4 in June and 1 in July.
I have found formulas that will tell me how many times a day of the week occurs in a given month, and formulas that will allocate the days across a date range to the months they fall in, but I need something that will put that together & say--within this date range, how many Tuesdays are in June and how many are in July?
I know that Excel can automatically look at a date & tell what day of the week it is... However, one complicating factor is that some classes will meet multiple times a week--is there some way to take that into consideration?
Another complicating factor is that if a class meeting would fall on a holiday, it would need to be taken into consideration. (So a class running from June 4, 2014 - July 29, 2014--assuming we closed the week of July 4--would meet 8 times, not 9. I would need the calculation to be able to factor that in.)
I am trying to keep it all as simple as possible, the fewer moving parts the better since data entry will be done by someone who won't be able to trace the source of an error. However, at this point, I would be happy for ANY solution!!!
Thank you so much for your help!
