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

1. ## 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.
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

2. ## 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)))))

3. ## 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. ## 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.

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

#### 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