 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 20061-2-3 @ function to Excel Assistance
Coworker just converted a 1-2-3 spreadsheet into Excel workbook. Onl thing that didn't convert was the 1-2-3 @ function for the date. I need to show the percentage of the year that has elasped from th start date of 10/01/05 to a date that I provide = % of year elasped. The 1-2-3 formula for this was: @DAYS(@DATE(2005,10,1),@DATEVALUE(+H3),3)/365. I'm a new user so detailed instruction would be helpful. I don't kno if everything goes in one column as in 1-2-3 or if it has to go int multiple columms. Thanks in advanc Could you explain what the parameter 3 does in your @DAYS( ) function? You seem to have: @DAYS(first_date, second_date, 3) Pete Unfortunately I didn't create that formula since I inherited the 1-2- spreadsheet and it had already been created. But, looking at it th (+H3) is the cell in 1-2-3 that the current day or day I select i entered. That is the date I need to be divided by my start date to ge the % of elasped time. It isn't always the current date so it's entere each month when the report is updated. I'm not sure about the 3)365 except that we use 365 days in a year. Thanks > Unfortunately I didn't create that formula since I inherited the 1-2-3 > spreadsheet and it had already been created. But, looking at it the [quoted text clipped - 12 lines] > CMA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32522 > View this thread: http://www.excelforum.com/showthread.php?threadid=523055 The ,3/365 means use 365 days as the basis for days in the year. @DAYS(start-date;end-date;[basis]) calculates the number of days between two dates using a specified day-count basis. Arguments start-date and end-date are date numbers. If start-date is earlier than end-date, the result of @DAYS is positive. If start-date is later than end-date, the result of @DAYS is negative. If start-date and end-date are the same, the result of @DAYS is 0. basis is an optional argument that specifies the type of day-count basis to use. basis is a value from the following table: basis Day-count basis 0 30/360; default if you omit the argument 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360 Examples @DAYS(@DATE(93;4;16),@DATE(93;9;25)) = 159, the number of days between April 16, 1993, and September 25, 1993, based on a 360-day year of twelve months, each with 30 days. @DAYS(@DATE(93;4;16),@DATE(93;9;25),1) = 162, the number of days between April 16, 1993, and September 25, 1993, based on the actual number of days in the months April through September. Similar @functions @DATEDIF calculates the number of years, months, or days between two dates. @D360 and @DAYS360 calculate the number of days between two dates, based on a 360-day year. @NETWORKDAYS calculates the number of days between two dates, excluding weekends and holidays. If you have two dates in Excel you can just subtract one from the other to get the difference in days - this can then be divided by 365 to convert to years. Hence your formula can be written in one cell as: =(H13-DATEVALUE("10/01/2005"))/365 This will give you fractions of a year elapsed since the reference date of 10/01/05 - I've done it this way so that you can easily change the reference date, but it assumes H13 contains a date in Excel format. Hope this helps. Pete Pete Thanks!!! I'm gonna try it in a few mins and see what happens. Interesting, both of my "helpers" are from the U Looks like we are close. I did the formula you provided and this was m result: -10582.5% It looks like it's 82.5% elasped since the start date but how do I ge rid of the number preceeding the 82.5%, and actually the boss woul probably rather not see the .5% and have it show 82% or round up/dow to the whole number. Thanks a million for the assistance Looks like we are close. I did the formula you provided and this was m result: -10582.5% It looks like it's 82.5% elasped since the start date but how do I ge rid of the number preceeding the 82.5%, and actually the boss woul probably rather not see the .5% and have it show 82% or round up/dow to the whole number. Thanks a million for the assistance No, this does not mean that 82.5% of a year has elapsed - think about it: it is now March and the reference date was in January of last year, so more than one year has elapsed. Alternatively, the reference date was 1st October, so we are now about half a year past that date (I don't know if you are working with mm/dd/yy or dd/mm/yy format dates). You need to put a date in cell H13 for it to work correctly. If you make it 20/03/2006, then you should get 1.189041, and you need to format this cell as percentage with no decimal places if you want it to show 119%. To follow the alternative date format, enter 03/20/2005 and you will get 47%. If you want to avoid spurious values, then change the formula to this: =IF(H13=0,"",(H13-DATEVALUE("10/01/2005"))/365) This will give you a blank result if there is nothing in cell H13. Hope this helps. Pete |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.