Elapsed time between 2 events

  • Thread starter Thread starter ArtDent
  • Start date Start date
A

ArtDent

I play the BBC Celebdaq game (a bit like stocks and shares/HSX)and write
players guides which are a free resource to other players.

I am working on an Excel 2000 tool and it is almost complete except for
time and date problems...

I need to calculate the dividend entitlement for players based on the
time they brought the shares and midnight on Thursday (when dividends
are paid out). Shares purchased earlier than the previous Friday gain
100% dividends, shares purchased after that time are paid on a sliding
scale from 100% to 0% based on the how long the shares are held. Shares
sold before midnight do not have dividends paid on them.

I also need a formula to calculate elapsed time between the purchase
time and the current time. This allows a comparison on share
performance to be made.

I think some sort of formula would be a good idea so the time is
entered in one cell and the date or day in another (If you see what I
mean).

This is a real learning experience for me as I think I bit off more
than I can chew on this bit. The other bits of the spreadsheet work
pretty well though. It would be nice if you included some idea about
how it worked so I can learn more about it or am I pushing my luck?


Thank you in advance for your help.

Art Dent

:confused:
 
XL dates are stored as integer offsets from a base date, and times
are stored as fractional days (e.g., 03:00 = 0.125), so you can use
them in normal math functions.

Assume:

A1: <date bought>
B1: <time bought>
A2: <date sold>
B2: <time sold>

then if the dividend "sliding scale" is continuous, the % to pay out
is just

C1: =MIN(((A2+B2)-(A1+B1))/7,1)

I can't tell from your description how exactly to determine whether
the dividend is earned at all (i.e, how to determine when the
valuation is made). Note that this will not take into account any
changes in daylight savings time, for instance.
 
Back
Top