Same time, next year....

B

Bobby

Hello,

I am working on a massive (evil) Double-Declining Balance AMT
spreadsheet...ugh!

The first issue I would like to address is that I have a list of
assets, all with different purchase dates in Column C. I would like
column D to compute the date of 3/31/XX, where XX is the NEXT instance
of the date 3/31. In other words,

Date Entered Date Desired
8/17/05 3/31/06
2/15/87 3/31/87
6/24/94 3/31/95

I've worked with dates before and created a pretty intense timesheet,
but I am at a loss as to where to begin for this particular
concern...any thoughts?

bobbie
 
D

DJH

Bobby said:
Hello,

I am working on a massive (evil) Double-Declining Balance AMT
spreadsheet...ugh!

The first issue I would like to address is that I have a list of
assets, all with different purchase dates in Column C. I would like
column D to compute the date of 3/31/XX, where XX is the NEXT instance
of the date 3/31. In other words,

Date Entered Date Desired
8/17/05 3/31/06
2/15/87 3/31/87
6/24/94 3/31/95

I've worked with dates before and created a pretty intense timesheet,
but I am at a loss as to where to begin for this particular
concern...any thoughts?

bobbie
Click to expand...
 
D

DJH

Try where`A1' is your reference date

=IF(AND(DAY(A1)<31,MONTH(A1)<4),DATE(YEAR(A1),3,31),DATE(YEAR(A1)+1,3,31))
 
B

Bob Phillips

=DATE(YEAR(A31)+(MONTH(A31)>3),3,31)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
R

Roger Govier

Hi Bobby

Try
=IF(DATEDIF(A1,DATE(YEAR(A1)+1,3,31),"m")<=11,DATE(YEAR(A1)+1,3,31),DATE(YEAR(A1),3,31))

Regards

Roger Govier
 
R

Ron Rosenfeld

Hello,

I am working on a massive (evil) Double-Declining Balance AMT
spreadsheet...ugh!

The first issue I would like to address is that I have a list of
assets, all with different purchase dates in Column C. I would like
column D to compute the date of 3/31/XX, where XX is the NEXT instance
of the date 3/31. In other words,

Date Entered Date Desired
8/17/05 3/31/06
2/15/87 3/31/87
6/24/94 3/31/95

I've worked with dates before and created a pretty intense timesheet,
but I am at a loss as to where to begin for this particular
concern...any thoughts?

bobbie
Click to expand...


=DATE(YEAR(A2)+(DATE(YEAR(A2),3,31)<=A2),3,31)

will give the NEXT 31 Mar as you specified.

So if Date Entered = 3/31/2005; Date Desired will be 3/31/2006.

If you do not want it to step up, change the comparison operator from '<=' to
'<'


--ron
 
Top