+ Reply to Thread
Results 1 to 4 of 4

Excel 2010 calculate time used with stop /start at given times

  1. #1
    Registered User
    Join Date
    10-02-2014
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    16

    Excel 2010 calculate time used with stop /start at given times

    I have project for pigeon racing. One function is to calculate the flying time used by the pigeon from release to home.
    Release time, usually in the morning, is different every time. And sometimes we have races where some pigeons come home the day after or 3rd day. The pigeon cannot fly in the night when it is dark, so we have given times when the counting of flying time is halted. The counted flying time is stopped at a given time in the evening and continues at a given time in the morning when it is light again. This period we call night time.
    I have searched my Excel 2010 all over, but cannot find anything that I can use. So I guess I need a macro. As I am still a beginner in making macros, I ask for help in this. But if it is a function/formula in Excel that I didn’t find and can be used, I would appreciate help with this also.
    I have in my spreadsheet already in cells (among other things):
    D3: function NOW ()
    D7: Calendar where I can pick release date
    E7: release time, written manually
    E8: Distance in km (123,456)
    C9: night time start, written manually (so far)
    E9: night time ending, written manually (so far)
    F12: formula showing actual time(not flying time) since release
    D12: counting of days between release day and actual day (day today), using INT(D3)-D7
    I3: Macro clock showing actual time (NOT AM-PM) (macro delivering only decimals)

    The result of this calculation is to be shown in cell E11. I want it to show the flying time “live” and counting before and after night time, but standing still during night time.

    attachments: Clip of spreadsheet and Macro clock
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,319

    Re: Excel 2010 calculate time used with stop /start at given times

    Since you have a date in D7 using dot separators, I created a named formula to convert it to an Excel date serial:

    ReleaseDate =DATE(RIGHT(Sheet1!$D$7,4),MID(Sheet1!$D$7,4,2),LEFT(Sheet1!$D$7,2))

    Then in cell D12 --> =D3-ReleaseDate

    and in cell E13 --> =IF(OR(ISBLANK(E7),(ISBLANK(E8))),"",ReleaseDate+(((($E$8*1000)/250/1440)+E7)+((((1-C9)+E9)*(INT(D3)-ReleaseDate)))))
    Attached Files Attached Files
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    10-02-2014
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    16

    Re: Excel 2010 calculate time used with stop /start at given times

    Sorry that I am late to answer...
    Thank You for very good answer. Very useful. The little piece of art that You made about day and days in the spreadsheet made me understand that IF-function can be used in a lot of ways. This again led to that I solved other issues in my spreadsheet also. Your little piece of art opened my eyes. I have after this had tunnel vision and only working with different IF solutions and learned new things after that.
    Again, Thank You.

  4. #4
    Registered User
    Join Date
    10-02-2014
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    16

    Re: Excel 2010 calculate time used with stop /start at given times

    Sorry that I am late to answer...
    Thank You for very good answer. Very useful. The little piece of art that You made about day and days in the spreadsheet made me understand that IF-function can be used in a lot of ways. This again led to that I solved other issues in my spreadsheet also. Your little piece of art opened my eyes. I have after this had tunnel vision and only working with different IF solutions and learned new things after that.
    Again, Thank You.

+ 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. Excel 2010 time start and time end defined by slicers
    By Shmulik_r in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-21-2014, 08:59 AM
  2. start stop times issues in excel
    By superchew in forum Excel General
    Replies: 7
    Last Post: 08-18-2013, 03:13 PM
  3. Start stop time in excel
    By superchew in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 08-17-2013, 10:51 AM
  4. Calculate hours worked from dropdown list for start and stop times
    By vanchopski in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-02-2012, 05:22 PM
  5. Replies: 0
    Last Post: 11-14-2011, 05:40 PM
  6. Replies: 1
    Last Post: 06-25-2010, 06:57 AM

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