1-2-3 @ function to Excel Assistance

[CMA]

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

 

[Pete_UK]

Could you explain what the parameter 3 does in your @DAYS( ) function?
You seem to have:

@DAYS(first_date, second_date, 3)

Pete

 

[CMA]

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

 

[ANDRE.TASSEL]

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
(+H3) is the cell in 1-2-3 that the current day or day I select is
entered. That is the date I need to be divided by my start date to get
the % of elasped time. It isn't always the current date so it's entered
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,

d: 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.

 

[Pete_UK]

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

 

[CMA]

Pete

Thanks!!! I'm gonna try it in a few mins and see what happens.

Interesting, both of my "helpers" are from the U

 

[CMA]

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

 

[CMA]

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

 

[Pete_UK]

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