Date/Time Calculations

[IGWright]

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]

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

 

[Fred Smith]

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.

 

[daddylonglegs]

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