hp 17bii+ dates calculation...

[JoePaul]

I am following the tutorial here:

http://h20000.www2.hp.com/bizsupport/TechSupport/Document.jsp?objectID=c01815531

and in example 3, when I press '365D' in step 1 I get an invalid date error message.

I would like to calculate dates into the future using a 360D calculation for real estate transactions. ie) lease expiry 6 months (180 days) into the future.

I imagine I would have to press '360D' in step 1, enter the current date in step 2, and 180 days in step 3, but

a) I can't press 360D in step 1 (invalid date error)
b) I can't press 180 and store it in 360D to calculate Date2

Is my calculator faulty or does it require a firmware upgrade?

[Don Shepherd]

Per the 17bii+ manual, pages 150-151, you first enter your two dates and store them in Date1 and Date2. Then press DAYS to calculate the actual days between the dates, or press 360D or 365D to calculate the number of days using a 360-day or 365-day year.

Example 3 is faulty.

Edited: 13 July 2013, 8:06 p.m.

[JoePaul]

From my understanding it's only possible to add actual days to dates using the 'Days' softmenu. Is this correct?

Either:
a) enter Date1, Days and calculate Date2 or
b) enter Date1, Date2 and calculate Days

Is it not possible to add 'months' to Date1? I would have thought it's possible to enter Date1, 360D (calculated using months * 60) and the calculator respond with Date2.

The way I am doing it now is as follows:
i) Store Date1 with today
ii) Store 540 into Days (for a 18 month lease)
iii) Calculate Date2
iv) See the value of 360D (in this case it's 531 so I require 9 more days)
v) Add 9 to the existing value of Days and store this in Days (i.e 549)
vi) Calculate Date2

It's almost easier using fingers to count out the months 18 times...

Why couldn't HP allow us to store 540 into 360D in step ii) above?

[Don Shepherd]

JoePaul, what you are asking is not in my area of expertise, sorry. Perhaps someone else will chime in here.

Don

[Katie Wasserman]

Quote:

---

From my understanding it's only possible to add actual days to dates using the 'Days' softmenu. Is this correct?

---

Yes.

However you could use the solver to do what you're doing manually, that would be:

DATE2=DATE(DATE1:2xMONTHSx30-DDAYS(DATE1:DATE(DATE1:MONTHSx30):3))

However this formula (and your method) fails for long time periods. For example 10 yeas (3600 days) from 7/13/2013 will be 7/12/2013 with you method. There are also problems with leap years, 12 months from 2/29/2012 is 3/1/2013 but I'm pretty sure you want this to be 2/28/2013.

A more complex solver formula could be created to get around both of these problems.

-Katie

[JoePaul]

Thank you for that solver equation! It works as intended. When writing lease agreements, I subtract 1 day from the move in date.

e.g.) Tenant moves in 14 July 2013 for 9 months. The lease end date is 1 day less than 9 months from the move in date (i.e. 13 April 2014).

Using your solver equation, I can easily account for this by subtracting 1 from the result shown.

Im sure it's possible to subtract 1 from the result (automatically) or to have a conditional expression that determines when long periods (e.g. 76, 120) apply and not to subtract 1 in this instance. Surprisingly, when using 80 months it adds a day!

p.s Leap years aren't an issue (I ignore them).

The example I used is:

months=9

date1=14.072013

date2=14.042014 (calculated)

months=60 (5 years)

date1=14.072013

date2=14.072018 (calculated)

months=76

date1=14.072013

date2=13.112019 (calculated)

months=80

date1=14.072013

date2=15.03202 (calculated - implies 15.032020)

months=120 (10 years)

date1=14.072013

date2=13.072023 (calculated)

Edited: 14 July 2013, 5:45 a.m.

[Katie Wasserman]

I think that this method is somewhat poorly defined and has offset problems that would be difficult to patch into a solver formula. Your 80 month example: 14/07/2013 results in 15/03/2020 (+ 1 day) but if you start one month later: 14/08/2013 will end up at 13/04/2020 (- 1 day).

Your best bet would be to create a solver formula by pulling apart the date into variables: month, day, year and manipulating these without using the DDAYS and DATE functions. I would try to do this as a programming exercise but I don't know how you want to deal with month-ends.

For example:

80 months from 31/08/2013: 30/04/2020 or 01/05/2020?

80 months from 30/06/2013: 29/02/2020 or 28/02/2020?

80 months from 30/09/2013: 30/05/2020 or 31/05/2020?

Edited: 14 July 2013, 11:34 a.m.