date problem

  • Thread starter Thread starter m&m
  • Start date Start date
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 !
 
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)
 
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...
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top