Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 +-------------------------------------------------------------------+ |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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date/Time Calculations
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 ToolsAddins). Given the cash flows and their dates, it will calculate your return. -- Regards, Fred "IGWright" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date/Time Calculations
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date/Time Calculations
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date/Time Calculations
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 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=543135 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex calculations | Excel Discussion (Misc queries) | |||
looping through a set of calculations | Excel Worksheet Functions | |||
date/time stamp | Excel Worksheet Functions | |||
Calculations crossing multiple sheets | Excel Discussion (Misc queries) | |||
Date/time range based calculations | Excel Discussion (Misc queries) |