Trying to validate date fields in some way.

Spencer Simmonds

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?

J. Craig Williams

Spencer,

 

Would you please share the sheet with me?

jcwill23@gmail.com

 

Craig

Spencer Simmonds

Hi Craig

 

Sure, thank you.  Do you need Admin rights?

 

Spencer

J. Craig Williams

Spencer and I took this conversation off line. Last I checked, he's still testing the solution we came up with.

 

The problem can be restated to this:

The cycle column contains a periodicity. Some of the time periods can overlap the end of the month, others won't. When the time period does go over the end of the month, Spencer's team would like to know which month to assign the work to. They do this by determining which month has more business days. 

Based on the start and end dates ([Period Start] and [Period End]), he'd like to calculate what goes in the [Month] column.

 

So, we're looking for number of business days in a particular month.

 

To resolve this, we created 4 new columns to hold the calculations. The formulas could be combined into fewer, but because we were switching back and forth between dates and numbers, debugging and maintence would be easier in the long run by keeping the formulas simpler.

 

The columns are (all examples are row 8): 

 

End-of-Month

This determines the last day of the month for the [Period Start] date, in case the period goes over and we need to know that date.

 

=IF(MONTH([Period Start]8) = 12, DATE((YEAR([Period Start]8) + 1), 1, 1) - 1, DATE(YEAR([Period Start]8), (MONTH([Period Start]8) + 1), 1) - 1)

 

The formula looks kind of funny (to me), but is pretty clever (I did not create it). It figures out whether the month is December - if it is, we need to get the first day of the following year and subtract 1. DATE is a function that takes text or numeric values for YEAR, MONTH, DAY and gives a date field.If we aren't in December, then the we just use next months first day and subtracts one.Again, only needed if we need it. 

To get the last day of a month, we get the first day of the following month and subtract one. More elegant than my initial solution.

 

PS-to-end

This determines the number of work days between [Period Start] and the [End-of-Month]

 

=NETWORKDAYS([Period Start]8, [End-of-Month]8) 

 

or the number of days this month. 

 

begin-toPF

This does the reverse and calculates back from the [Period Finish] to the first day of next month

=NETWORKDAYS(DATE(YEAR([Period Finish]8), MONTH([Period Finish]8), 1), [Period Finish]8)

Again, we use the DATE formula to build back up a date value from numbers. 

 

Note: For time periods that do not cross the end of month boundary, the last two columns will have the same value. 

 

MonthNum

This is the important one, were we figure out which month to use.

 

=IF(MONTH([Period Start]8) = MONTH([Period Finish]8), MONTH([Period Start]8), IF([PS-to-end]8 > [begin-to-PF]8, MONTH([Period Start]8), MONTH([Period Finish]8)))

 

If both dates are in the same month, then we use the month of [Period Start].

But we could use the other one, doesn't matter.

If they are different, we check if PS-to-end (month 1) is bigger than begin-to-PF (month 2), and use the larger for the two's month. 

 

Note: If there are the same number of days in each month, we use month 2. We could change it to use month 1 easily. 

 

Now that we know which month (by number) we can determine the Month in text.

 

=IF(MonthNum8 = 12, "December", IF(MonthNum8 = 11, "November", "otherwise"))

 

where the formula is not complete but you should get the idea.

 

Here's a screen capture with some data.

 

 

I hope my explanation is clear and helpful.

 

Craig

 

JohnHinkle

wow, great work!!