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.
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.
- Thanks, I found this as an imperfect solution to a problem searching various forums. Now I understand why it doesn't work some of the time.
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.
- Thanks Peter, that looks amazing. It's solved my issue.
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.
- Unfortunately they use a combo of desktop version, SharePoint and Teams, and don't distinguish between them. I think I will keep the current solution in place for a week or so to 'train' them on how to enter a date into Excel then remove it,