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 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 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,