Hi I have created a sheet so that our Admin team here can accurately record sales information and can then run reports to calculate commission figures.
I can share this sheet if anyone would like to see it.
I am using a lot of conditional formatting to avoid input errors so that the output reports are accurate and correct. I have highlighted a possible user error scenario that I can't figure a way to highlight by conditional formatting and would result in data being ommited from the report.
Each time period will have a different row. Each has a dropdown list in a "Month" column and the options are the 12 months of the year. There are then "Period Start" and a "Period Finish" columns. In a "Cycle" column there are four options in a drop down list. Monthly, Twice Monthly, Weekly and Fortnightly. When the Cycle is set to Monthly or Twice Monthly then in theory I wouldn't need the "Month" column as the dates between the Period Start and Period Finish would always be in the same calendar month.
If however the Cycle was set to Weekly or Fortnightly it is then possible for the period to span two different months as half of one week might be in October but the second half of that week might be the beginning of November. This is why I used the Month column as well. This way, in my report I can specifiy when the Period Start is from a certain date and the period Finish is within a certain date AND the Month is a certain month then return the results.
The reason we do this is because if a Weekly or Twice Weekly period spans two months then we need to choose which month the period relates to for commission purposes, hence the Month column. The problem comes if the data is entered incorrectly. If for example the Period start date is 01/11/15 and the Period Finish date is 30/11/15 AND the Month column is set to November then this will work perfectly. If Month is however set to October in error, this row will be ommitted from the report. I have just thought of a possible way where this could work but I would need some help with the formula if this was possible. Instead of the Month column being a dropdown list, could this be populated based on a combined IF and NETWORKDAYS formula?
For example the formula calculates how many working days there are in the period and whatever month the majority of the working days fall within returns the result of that month in the Month column. If for example it was a weekly Cycle and between the Period Start and Period End dates there were 5 working days, 3 of which were the last three working days of October (Monday, Tuesday and Wednesday) but the other two days (Thursday and Friday) were the first two working days of November then the Month column would return "October" as the majority of working days for that period are in October. Something like this would help avoid user error and we could report accurately. I hope this makes sense on what I am trying to achieve.
Is it possible to validate or use conditional formatting to achieve this?