I have an application where users are entering start and end times as time values only in a timesheet  (i.e. 22:37, not 12/9/12 22:37) but values are stored in the database as datetime.The user only sees the time portion of the datetime - this is to simplify data entry by not requiring them to enter the date portion when doing heads down data entry.

The timesheets are for shifts, and the shifts run from 08:00 to 20:00 and 20:00 to 08:00.

The date portion of the start and end times are defaulted from the date on which the shift started. There is code in the form that enters the start time for each row as either the start of the shift or the latest end time of any previous rows entered.

On the 20:00 to 8:00 shifts, then, at some point they are going to enter a start or end time which is after midnight and is in fact on the next day - e.g a start of 23:20 and end of 01:10.

So I wrote some more code that inspects the time entered, and if it is earlier than the start date/time of the shift prompts the user to change it to the next day or to correct it if it is a data entry error.

Here's the dilemma ...

The logical place to put this to me is in the BeforeCellUpdate event. The problem is that in that event, I can't add a day to the entry to move it from the shift start day to the next day because everything in the BeforeCellUpdate event isread-only.

I can put it in the AfterCellUpdate event but the problem with that is that if a user answers the prompt to change the day with "No" they can leave invalid data in the row.

I can't help but think there's a way to do this in the BeforeCellUpdate. If it's a datetime that's "too early", ask the user if it is supposed to be after midnight on the next day, and if they say no clear the cell and make them try again.

Can anyone suggest something I may be missing here?