staff rota date and day calculation

If I understand you correctly, you could simply type the date, for example 01/01 in cell A1 and the corresponding day in B1 and then highlight both by left clicking A1, hold the mouse button and drag to B1 and then release the button. In the resulting highlighted cells, hover over the very bottom right hand corner of B1 until your cursor changes to a cross and then drag down as far as you need to for each month (ie. to row 31 for January). Hope that helps.

Please see attached. Is this what you meant?

thats perfect! thanks a million, by chance is there anyway i could make it skip weekends and only show weekdays, just for reference

Please see attached.

Just a small point on the original version......if you use this formula

=IF($A5="","",IF(MONTH($A5+1)>$A$2,"",$A5+1))

Then when A2=12 that won't prevent the next month showing because 1 < 12

Of course as you know that's only a problem for December you could just keep A36 blank as you won't need that cell for any month......or a subtle change.....

=IF($A5="","",IF(MONTH($A5+1)<>$A$2,"",$A5+1))

You need to use the same sort of change to the weekday formula to make December work, although you could possibly utilise WORKDAY function, e.g. in A6 copied down

=IF($A5="","",IF(MONTH(WORKDAY(A5,1))<>A$2,"",WORKDAY(A5,1)))

WORKDAY is built in in Excel 2007 and later but for earlier versions you need Analysis ToolPak add-in available

wow thats great, i really need to read up on excel, seems im gonna be using it alot more. time to learn i think!. thanks a million!