+ Reply to Thread
Results 1 to 3 of 3

Time as an elapsed value, not date and time or period between two date times

  1. #1
    Registered User
    Join Date
    05-31-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010 (work) and 2013 (Home)
    Posts
    2

    Time as an elapsed value, not date and time or period between two date times

    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

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Time as an elapsed value, not date and time or period between two date times

    If you want 36.5 hours displayed as 36:30, then you can apply a custom format to the cell of [hh]:mm - the square brackets around the hh part prevents the hours from being wrapped at 24. Forget what is displayed in the formula bar. So, just format all the cells that this is likely to apply to in that way.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    05-31-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010 (work) and 2013 (Home)
    Posts
    2

    Re: Time as an elapsed value, not date and time or period between two date times

    Thanks, that worked.

    Stephen

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1