Forum Discussion
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 not believe how hard this seems to be for them, or the creative ways in which they enter the date and time and (it seems) anything else they can think of!
I've asked them to enter it in as dd/mm/yyyy hh:mm and used Format Cells to specify [$en-GB]dd mmm yyyy hh:mm (so we can see the month and avoid any mix-ups with US format) and custom Data Validation with the formula =MOD(A3,2) which seems to work half the time. The problem is when the time is 00:00 (which it defaults to when not entered with the date, not a problem, desirable even), some of the time this appears to be unacceptable and you have to put a different time in (we've been using 00:01). Any ideas as to why and a way around it?
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.
What is the purpose of the Data Validation rule with formula =MOD(A3,2) ?
To make sure the date and time is entered in a specific format. It means if you don't type a date (and time) in that cell using the specified format, then an error message pops up and you have to amend or cancel your entry.
I don't understand how that would work.
Dates and times are stored in Excel as numbers: a date is the number of days since the beginning of the year 1900 (or 1904 if you use the 1904 date system), and a time is a fraction of 1 day (for example 6:00 is stored as 0.25 since it is 6/24 day).
The formula =MOD(A3, 2) will cause data validation to fail for midnight on days that are stored as an even number. That doesn't make sense to me.
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.
