Dec 14 2023 12:34 PM
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?
Dec 14 2023 01:06 PM
What is the purpose of the Data Validation rule with formula =MOD(A3,2) ?
Dec 14 2023 02:28 PM - edited Dec 14 2023 02:31 PM
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.
Dec 14 2023 02:34 PM
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.
Dec 14 2023 03:21 PM
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.
Dec 15 2023 01:54 AM
Dec 15 2023 01:54 AM
Dec 15 2023 04:19 AM
If all users use the desktop version of Excel for Windows or Mac, you could use VBA code (the Worksheet_Change event procedure) to check that the entry is a date/time value within the specified range.
Dec 15 2023 10:23 AM
Dec 15 2023 02:43 PM
SolutionI 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.
Jan 03 2024 01:24 AM
Dec 15 2023 02:43 PM
SolutionI 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.