Date/Time Calculations

My appologies if I've posted in the wrong area. I'm new to the Forum and did a search, but couldn't find what I was looking for.

I have an Excel Spreadsheet I use for Day Trading. I am trying to figure out a way of calculating the profit/time ratio for trades and I think I'm in over my head.

I've attached a copy of the sheet.


Thank-you,


Ian

Attachments don't work because this newsgroup does not support them. Most people
won't open them anyways for fear of viruses. Simply describe your problem in the
body, and you'll get quick, accurate responses.

You most likely want the XIRR function (it's in the Analysis Tookpak, which you
may have to install using Tools>Addins). Given the cash flows and their dates,
it will calculate your return.

--
Regards,
Fred


"IGWright" <[email protected]> wrote in
message news:[email protected]...
>
> My appologies if I've posted in the wrong area. I'm new to the Forum and
> did a search, but couldn't find what I was looking for.
>
> I have an Excel Spreadsheet I use for Day Trading. I am trying to
> figure out a way of calculating the profit/time ratio for trades and I
> think I'm in over my head.
>
> I've attached a copy of the sheet.
>
>
> Thank-you,
>
>
> Ian
>
>
> +-------------------------------------------------------------------+
> |Filename: Books.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4777 |
> +-------------------------------------------------------------------+
>
> --
> IGWright
> ------------------------------------------------------------------------
> IGWright's Profile:
> http://www.excelforum.com/member.php...o&userid=34545
> View this thread: http://www.excelforum.com/showthread...hreadid=543135
>

Thanks for jumping in. Basically, I'm taking information from my Daily Brokerage Statement and pasting it into Excel.

Everything is working, but I've noticed that calculating the elapsed amount of time between one Date/Time and another doesn't seem to be a simple process (Unless I'm missing something, which is entirely possible.).

The format of the Date and Time is thus:
2006-05-16, 09:43:18
2006-05-16, 11:57:52

What I'm looking for is a formula for calculating the amount of time between the two dates. If I can get that far, I can figure out the rest myself. I figure the easiest way for me is to have the information in seconds, then I can simply multiply that figure by what by the appropriate amount to create minutes, hours, days, weeks and years.



Tell me what you think.


Ian

There are several ways to do this, but here's what I would do:

1. Convert your text into a true Excel date/time, using the following formula:
=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,13,2),MID(A1,16,2),MID(A1,19,2))

2. Display this in any format you like, such as a custom format of:
mm/dd/yyyy hh:mm:ss

3. Excel date/times are the number of days since 1/1/1900. Time is a fraction of
a day. Once converted, you can use any math on them. For example, the difference
between the two (in days) is:
=a2-a1 [format as a number]

4. If you really want the difference in seconds, multiply by the number of
seconds in a day (86400). However, I suspect you will be better off working with
the date/time in native Excel format.

--
Regards,
Fred


"IGWright" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for jumping in. Basically, I'm taking information from my Daily
> Brokerage Statement and pasting it into Excel.
>
> Everything is working, but I've noticed that calculating the elapsed
> amount of time between one Date/Time and another doesn't seem to be a
> simple process (Unless I'm missing something, which is entirely
> possible.).
>
> The format of the Date and Time is thus:
> 2006-05-16, 09:43:18
> 2006-05-16, 11:57:52
>
> What I'm looking for is a formula for calculating the amount of time
> between the two dates. If I can get that far, I can figure out the rest
> myself. I figure the easiest way for me is to have the information in
> seconds, then I can simply multiply that figure by what by the
> appropriate amount to create minutes, hours, days, weeks and years.
>
>
>
> Tell me what you think.
>
>
> Ian
>
>
> --
> IGWright
> ------------------------------------------------------------------------
> IGWright's Profile:
> http://www.excelforum.com/member.php...o&userid=34545
> View this thread: http://www.excelforum.com/showthread...hreadid=543135
>

Your date/time format only differs by one character (the comma) from a recognisable excel date/time format so you can easily convert by using this formula

=SUBSTITUTE(A1,",","")+0

If you want to subtract the time/date in A1 from that in B1 and convert to seconds....

=(SUBSTITUTE(B1,",","")-SUBSTITUTE(A1,",",""))*86400

that formula should give you a result of 8074 for your example

..although, as Fred says, you may be better off omitting the *86400 and just formatting as time....., i.e. use just

=SUBSTITUTE(B1,",","")-SUBSTITUTE(A1,",","")

custom format as [h]:mm:ss to give an answer of 2:14:34