last day of the month formula

[smiley0q0]

in one of my excel spreadsheets i have the days of the month, here are some of my formulea

(cell E3)= manually typed, first working date of the month
(cell G3)=IF(WEEKDAY(E3)=6,E3+3,E3+1)
(cell I3)=IF(WEEKDAY(G3)=6,G3+3,G3+1) etc...

I would like it to stop on the last day of the month. i need it to be able to do any month of the year. any ideas on how to do this?

Thanks,
Smiley

 

[JGillespie]

Assuming first date is entered in A3 and all other dates want to be shown in columns to right try entering this formula in cell B3 and replicating across 30 columns:


=IF(A3=&quot;&quot;,&quot;&quot;,IF(MONTH(A3)<>MONTH(A3+1),&quot;&quot;,IF(WEEKDAY(A3)=5,A3+3,A3+1)))

(it is all on one line)

basically it checks to see if there is a value in the previous column. If there is it checks to see if it is the last day of the month by comparing the month part of the date with the month part of the next day. Finally it performs your original calculation.

hope it helps. Jammie Gillespie
j-gillespie@s-cheshire.ac.uk