Forum Discussion
Using MOD in Data Validation
I wasn't suggesting that you should forget to record the hours, just that the user input might be better supported if the two elements were collected separately. For example:
I went fairly wild with the number formatting but the idea was to show that you could have your cake and eat it when it comes to presenting usable data.
I would be tempted to split the date and time into two cells that may be validated individually using date and time ranges within data validation. As HansVogelaar points out, accepting alternate days does not really make sense and is probably based upon a misunderstanding of the MOD function.
Another idea. If the range of acceptable dates were limited to a few days about the current date you could used a validation list and allow the day to be picked out using a dropdown.
- I've thought of that, the trouble is we need to be able to measure in hours. We have three Date/Time columns, one for presentation/arrivals, one for when medically fit for assessment, and one for when assessed. The assessment must take place within four hours of being medically fit. So if someone is fit at 10pm, they must be assessed by 2am. Going over that four hours is a breach and we need to see when that happens, and also provide our current and over time average waiting times. This is hard when someone types "Friday the 15th of December at 3pm" in the date & time cell. I could split the time and date, but then that makes it much harder to measure.
