+ Reply to Thread
Results 1 to 8 of 8

Adding time and copying rows of data to a separate sheet

  1. #1
    Registered User
    Join Date
    10-27-2014
    Location
    Cotswolds
    MS-Off Ver
    Excel for Mac 2011 14.4.5
    Posts
    11

    Smile Adding time and copying rows of data to a separate sheet

    I need help with several of the formulas in this document.
    The first one is the time on sheet 1. Pilots have to input their start and finish times along with all the other data. In column M it should calculate the total time on duty. The formula I have works fine but only if they don't work into the following day. However as you can see in row 91 it returns an incorrect result. What is the formula to incorporate working into the next day?

    The pilots will be using sheet 1 on a daily basis to input their details. On sheet 2 I want it to autofill based on the date that they put into cell O4. The idea is that they have to report their duty information for the last 28 days. I want the pilots to be able to put the date of the report in cell O4 which will then look at sheet 1 and then populate the fields in sheet 2 to show the information for the last 28 days. The date that gets entered into O4 will be the last date of the report on sheet 2 so cell A49 will match with O4.

    I only have very limited knowledge with formulas and whilst I like to have a go I have spent days on this so any help would be greatly appreciated
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Adding time and copying rows of data to a separate sheet

    Hi,

    See attached.

    For times which straddle midnight you need to include an adjustment. e.g. in M4

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    On sheet 2 I've just added the first row of formulae. Just copy them down.
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-27-2014
    Location
    Cotswolds
    MS-Off Ver
    Excel for Mac 2011 14.4.5
    Posts
    11

    Re: Adding time and copying rows of data to a separate sheet

    You are amazing!!!! Thanks very much

  4. #4
    Registered User
    Join Date
    10-27-2014
    Location
    Cotswolds
    MS-Off Ver
    Excel for Mac 2011 14.4.5
    Posts
    11

    Re: Adding time and copying rows of data to a separate sheet

    Can you recommend any books that I can read to improve my formula knowledge that are written in really simple language. I am OK at the basics so don't need the real beginners guide but just something that explains the more complicated ones in simpler terms. Thanks

  5. #5
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: Adding time and copying rows of data to a separate sheet

    Excel forums are the best books there are.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  6. #6
    Registered User
    Join Date
    10-27-2014
    Location
    Cotswolds
    MS-Off Ver
    Excel for Mac 2011 14.4.5
    Posts
    11

    Re: Adding time and copying rows of data to a separate sheet

    Sorry me again!

    One last thing I don't understand why on sheet 2 cells C53 and C54 don't add up properly. I'm sure it has something to do with the time but I can't seem to find the answer can you shed any light as I am sure its probably very simple?

    Many Thanks
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-27-2014
    Location
    Cotswolds
    MS-Off Ver
    Excel for Mac 2011 14.4.5
    Posts
    11

    Re: Adding time and copying rows of data to a separate sheet

    Typical the minute I post and then go back to it I find the solution! I was right and i just needed to make sure all the cells were formatted the same. :-) Getting there slowly

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Adding time and copying rows of data to a separate sheet

    Hi,

    Hope I'm not teaching grandma to suck eggs, but when you see a time in a cell you're seeing how a cell **Appears** (i.e. formatted) not what is actually in the cell.

    Times are represented as a fraction of 24 hours. e.g. 0.25 = 6:00 (i.e. a.m). 0.66667 = 16:00 (i.e. p.m)
    When you add up dates which are formatted with the standard time format see M39 for example, what you are seeing is the decimal part of the underlying number.

    For instance at the moment although in M39 you SEE 14 hours 54 minutes, the underlying number is actually 1.6208 (with the cell selected hit F2 and then F9 to see this - don't hit the enter otherwise you'll overwrite the formula). The standard time format only evaluates the decimal part of the number, i.e. .6208 and shows the 14:54

    The total hours for M39 should be over 38, or to be precise 24 hours (from the integer 1 meaning one whole day in the number 1.6208) plus 14 hours.

    SO whenever you sum a range of times and you want to see say the total number of hours you need to format the cell differently.
    Use a Custom format and specify

    [h]:mm

    putting the 'h' in square makes the cell display the sum of hours.

    You'll then see 38:54 in M39. Apply the same format to all your total time cells including C53:C54 as well as M16, M28, M39 & M50

+ 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. Copying Data from one Sheet to another and adding rows to accomodate
    By whammond597 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-16-2014, 04:59 PM
  2. Replies: 1
    Last Post: 08-13-2013, 06:16 PM
  3. copying data from many files and adding to a separate file (PLEASE HELP!)
    By etodem in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-13-2013, 05:46 PM
  4. Selecting and Copying separate rows of data
    By tangobravo33 in forum Excel General
    Replies: 4
    Last Post: 06-27-2011, 11:11 AM
  5. Replies: 3
    Last Post: 08-12-2010, 03:45 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