Auto FIll Weekdays dates

[Terapixels]

Hi All,

I need to create a spreadsheet that automatically fills in the
dates(weekdays
only) across the top. There are six separate periods to be inputted.

The user inputs the start and end date of each period.

Any ideas?

TIA

Tera


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Earl Kiosterud]

Tera,

Put in the first date. Right-drag the Fill Handle, and select "Fill
Weekdays." XL2002.

 

[Terapixels]

Many thanks. I should have mentioned that I would like the spreadsheet
to fill in the cells automatically without further user input. Is this
possible?

Regards,

Tera.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Peo Sjoblom]

Yes it is but you would need to set it up which is not automatic.
It could be done either by using a worksheet event macro that would
excute when the user has pressed the enter key after typing in the date
or by using an IF formula with some testing for weekdays copied accross.
Since it's not a 10 second solution I'd suggest you do a google seach fro
excel even macros

 

[Earl Kiosterud]

Tera,

If I understand the question properly, you want the user to supply a start
date and end date, and have the worksheet provide weekdays across the sheet.
How far it goes across the sheet depends on the end date. Peo has touched
on this. If there will be nothing on the sheet beyond the end date column,
then formulas could be used. But if there will be other stuff beyond the
last date column (Row totals, other data, etc.), then it's different, and I
think a macro will have to insert columns, provide the dates, etc. A
somewhat specialized solution. Supply info.

 

[Terapixels]

Earl,

There are no other info at the end of the row. The user will supply the
start and end dates of each of the six periods. The dates should be
presented across row A without gaps between the peeriods. Many thanks.

Tera

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Earl Kiosterud]

Tera,

Set up two cells and name them StartDate and EndDate. This is set up for
StartDate of Monday.

B1: =StartDate
C1: ==IF(B1<>"",IF(B1+1<=EndDate,B1+1,""),"")
Copy (Fill Handle) to F1.
You now have one week, based on start and end dates.
G1: =IF(F1<>"",IF(F1+3<=EndDate,F1+3,""),"") Monday date
Copy C1:F1, paste to H1:K1.
You now have the second week of date formulas, G1:K1, Mon-Fri
Copy G1:K1, paste from L1 to right edge of worksheet:
Select and Copy G1:K1. Copy.
Select L1, Ctrl-Shift-RightArrow
Paste.