I’m leading a project at work where we are transitioning our social workers from a manual timekeeping process to our IntelliTime system which is an automated, electronic timekeeping system.

To help facilitate this transition for the social workers, I developed an Excel spreadsheet where they can enter their pager pay. If they are on call over a weeknight and into the next morning, they can enter their total pager pay hours on the day their on call ends. For example, if I was on call Monday evening into Tuesday morning, I would have 7.5 hours of pager pay for Monday (4:30 p.m. – 11:59 p.m. Monday) and 8 hours of pager pay on Tuesday (12:00 a.m. – 7:59 a.m.) on Tuesday or a total of 15.5 hours which I could code on my timecard for Tuesday.

The enhancement I’m trying to come up with is for the weekends. When a social worker is on call over the weekend, I currently have them entering each day separately on their spreadsheet (Friday, Saturday, Sunday, and Monday) because they can’t code more than 24 hours to one day in IntelliTime. So, what I’m looking for is either a formula, or, likely, it will need to be a macro, that would allow them to enter one start date and time and one end date and time for their weekend on call. Excel either through formula or macro or combination of the two would split out their hours for each individual day of the weekend. I attached a worksheet that shows an example of current state and what I’m trying to accomplish.

I'd appreciate any help or guidance anyone out there may be able to provide.

Thank you in advance!