+ Reply to Thread
Results 1 to 5 of 5

calculating time

  1. #1
    Registered User
    Join Date
    11-29-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    calculating time

    gentlemen and scholars...I am looking for some advice. I have a spreadsheet where my crews log in and log out at each site. this is done in 24 hour format. I have a separate column cell where I would like to calculate the total time spent on site...or time out minus time in. preferable I would like this total to be in minutes. I can get a value in hours and minutes IF my time out is greater than my time in. for example...time in is 07:30...time out is 08:45 my total is 1:15. however,. if my time in is, say, 9:50 and my time out is 1:30 I get no value in my total cell...it says "dates and times are negative or too large to display as ######.

    thanks in advance! oh...Office 2010

  2. #2
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: calculating time

    It sounds like the 1:30 should be 13:30 which is why you are having a problem.

    locluxul.png

  3. #3
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,835

    Re: calculating time

    Please Login or Register  to view this content.
    If someone has helped you then please add to their Reputation

  4. #4
    Registered User
    Join Date
    11-29-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: calculating time

    james...in reply to your comment...my 1:30 is an am time trying to subtract from a 9:50 pm. this is why I get a negative number

    PJ...wow...I think you are relying on me knowing what I am doing! lol
    I will try to implement your formulas into my spread sheet.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,448

    Re: calculating time

    I would probably start here: http://www.cpearson.com/excel/datetime.htm to get an understanding of how Excel handles dates/times.

    Since you want to be able to subtract times across midnight, my first thought would be to enter dates and times together. If you will have the start time entered as 31 Dec. 2014 9:35 PM and end time as 1 Jan 2015 1:30 AM (for example), it should be as simple as subtracting end time - start time to get "elapsed days" worked (since I don't expect any 24+ hour shifts, these numbers will come out as fractions of a day).

    These results can be left as fractions of a day and displayed in the desired format (either elapsed hours or elapsed minutes -- see number formatting options) or can be converted to fractional hours or fractional minutes by multiplying the result by a suitable conversion factor. *24 hours/day to get fractional hours, *24 hours/day *60 minutes/hour= *1440 minutes/day to get fractional minutes. It all starts by understanding how Excel handles dates/times as fractional days.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating staff roster time to hours, depending on time worked.
    By cookiet in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-16-2013, 10:25 AM
  2. Replies: 3
    Last Post: 11-21-2012, 05:47 AM
  3. Formula for Calculating Paid Time Off for fiscal anniversary date real time
    By 168rockwood in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-07-2012, 04:15 PM
  4. Calculating END time based on start time, breaks, and hours to complete
    By sweedey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-01-2012, 11:29 AM
  5. Excel Formulas for Calculating Straight, Over Time & Double Time in Cost Estimating
    By redhairredhair in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-23-2010, 09:06 PM

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