Excel 2007 : Adding Time and Duration

Hello,

I am trying to add two time values and have thus far been unsuccessful:

The first value is a "Start Time" which can be expressed as 13:00 (military time = 1 PM).

The Second Value is the duration which is expressed in seconds - 3600 (as in 3600 seconds = 60 minutes).

What I would like to calculate is the "Stop Time" for this event.

To Elaborate: Start Time + Duration = Stop Time
For this example the expected result would be 13:00 + 3600 seconds = 14:00

I have tried every time formatting variation I could think of, however the "duration" value is always displayed as a time and not a duration.

Any help with this will be greatly appreciated.

Hi,

Welcome to the forum, how about

=A1+(B1/86400)

The 3600 you want to display is not a time...but, perhaps a custom number format can help.

Select the cell
<format><cells><number tab>
Category: Custom
Type: General" sec"
Click [OK]

Now, 3600 will display as: 3600 sec

Next...To Excel, time is calculated as a percentage of a day.
Noon is 0.5 (half a day)

Your formula will need to convert the duration (in seconds) to a percentage of a day.

Try this to calculate StopTime:
=+A1+B1/(60*60*24)

(60 sec/min * 60 min/hr * 24 hrs/day)


Is that something you can work with?

In D2 and copy down,

=COUNTIF(C$2:C2, ">" & A2)

... counts the calls in progress including the given call. It tells you that when the call on line 13 started, it became the eighth concurrent call, the max that occurred.

That approach isn't going to scale well for 100,000 calls.

I don't see a simple formula solution at a glance, though there may well be one.

It should be easy and fast in VBA, though; can you post a larger sample of data (a few thousand calls?)

Hi,

Maybe an alternative?

=COUNTIF(C2:C16,"<"&MAX(A2:A16))