date problem

M

m&m

Dear experts,

i got a question on date generation :
if I input 1-Jun-04 in cell B1 , I would like it to automatically display
1-Jul-04 in cell C1 .
If 1 input 30-Jun-04 in cell B2 , it will automatically display 31-Jul-04 in
cell C2.

any formula can do it ?

Thanks in advanced !
 
H

Harald Staff

Hi

C1:
=DATE(YEAR(B1),MONTH(B1)+1,1)
C2:
=DATE(YEAR(B2),MONTH(B2)+2,0)
Optional B2 formula (for automatic "last of B1 month"):
=DATE(YEAR(B1),MONTH(B1)+1,0)
 
M

m&m

thank you so much for your help !!
Harald Staff said:
Hi

C1:
=DATE(YEAR(B1),MONTH(B1)+1,1)
C2:
=DATE(YEAR(B2),MONTH(B2)+2,0)
Optional B2 formula (for automatic "last of B1 month"):
=DATE(YEAR(B1),MONTH(B1)+1,0)

--
HTH. Best wishes Harald
Followup to newsgroup only please

31-Jul-04
Click to expand...
 
N

Norman Harker

Hi m&m!

If I view your two questions independently:

C1:
=DATE(YEAR(B1),MONTH(B1)+1,DAY(B1))
C2:
=DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+2
,0))))

Both formulas can be copied across the columns and will return the
first and last days of consecutive months.

But if I have the entry in C1 as a given, the C2 formula becomes
easier:
=DATE(YEAR(C1),MONTH(C1)+1,0)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

Doh!

My references to A1 should habe been to B2
Harald's approach is better for returning the last day of the month;
it's simpler. Mine was a general purpose approach for returning the
same day or the last day if that day doesn't exist.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top