Periodically I get hit by a request to provide a calculation of times as a number of hours and minutes in Excel. I have yet find a workable solution for those times when the number of hours goes over 24.

Example 1:

A person works 5 days in a week (Monday to Friday) and over those 5 days should work 36.5 hours. There hours of work each day are logged and diplayed by the timecard system. They need permission from their line manager to go over 36.5 hours in one week. So, on Friday morning they need to work out how many hours they have done that week then subtract that from 36.5 to get how many hours they can work on Friday before they go over 36.5 hours for the whole week then they estimate how many hours work they have that day and either ask their line manager for permission (saying how many more hours they have to work) to work over or reschedule work into the following week. It is not practially possible for them to just work 7.3 hours each day as demand rises and falls and they cannot stay at work unless there is work to do and cannot leave if there is work to be done. In theory they work a mixture of short and long days, in practice they work a mixture of long and longer days and have to ask permission to work over. Working this out by hand each week is taking too much time so they ant to just be able to enter their hours for Monday through Thursday into a spreadsheet and have it spit out how many hours and minutes they've worked and how many they have left. Unfortunately as soon as the number of hours goes over 24 Excel increments the day part of the date time and subtracts 24 hours (because even though it's not entered or displayed any time you enter is treated as that wall clock time on 01/01/1900, when you add them up if it goes over 23:59 it moves to 02/01/1900 and subtracts 24:00 from the time portion).

Example 2

A task has a certain number (>24) of hours allocated to it. It will be worked on intermittantly over a period of days or weeks. The person doing the task shall log their time on the task each day they work on it. A running total and count down is required to show time burned and time remaining so that time usage can be tracked against progress and more time can be requested (i.e. more funding to pay for that time) if it looks like they will require more.

Any suggestions how to do this?

The only solution I've used with remotely works is to have the user enter hours and minutes into separate cells then convert to minutes for the calculation and back again (again, two cells) to display. Very fiddly to use.

Thanks

Stephen