Forum Discussion

Copper Contributor

Dec 14, 2023

Solved

Using MOD in Data Validation

Hey all, I hope you can help. I've got a Workbook that logs mental health presentations at a hospital. I need people to enter the date and time in a column in the format dd/mm/yyyy hh:mm. You would n...

excel

Formulas and Functions

PeterBartholomew1
Dec 15, 2023
Kirkoid
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.
Validating elapsed times.xlsx20 KB

PeterBartholomew1

Silver Contributor

Dec 14, 2023

Kirkoid

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.

Kirkoid

Copper Contributor

Dec 15, 2023

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.