# Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need assistance setting up a time sheet formula where I can create a column of contiguous dates down the left hand side of the spread sheet. So I can copy a contiguous column of dates, rather than entering each date manually. I would only need to have work days in the list, ie Monday to Friday inc. with a couple of spare lines between each week.
Solved by Z. Y. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 13/06/2018 - 10:06
Hi…Welcome to Got It Pro
Excelchat Expert 13/06/2018 - 10:07
So you would like to enter the first date and then a formula to just fill down the contiguous dates excluding weekends, is that right?
User 13/06/2018 - 10:08
Yes thats correct
User 13/06/2018 - 10:08
If the first date could be 1/1/2018
Excelchat Expert 13/06/2018 - 10:09
Ok...and will it be ok if I leave the Sat & Sun rows blanks between weeks through the formula?
User 13/06/2018 - 10:09
Yes thats fine, so I have a couple of spare rows if needed for overtime allotments,
User 13/06/2018 - 10:12
Is it possible to include the days of the week in date column as well please.
Excelchat Expert 13/06/2018 - 10:12
ok...you mean something like...Monday 12th Jan, 2018 ?
User 13/06/2018 - 10:13
Correct
User 13/06/2018 - 10:13
That looks good
Excelchat Expert 13/06/2018 - 10:14
not yet...
Excelchat Expert 13/06/2018 - 10:14
Will need to still amend the formula to fill the bottom rows
Excelchat Expert 13/06/2018 - 10:18
Ok..I have a suggestion
Excelchat Expert 13/06/2018 - 10:18
let me know if it will work
Excelchat Expert 13/06/2018 - 10:19
What about just putting all the dates and just doing conditional format to conceal/hide the Sat and Sun cells
User 13/06/2018 - 10:21
Yes that sounds fine.
Excelchat Expert 13/06/2018 - 10:22
ok
User 13/06/2018 - 10:23
Is this formula easy to copy into my own work sheet?
User 13/06/2018 - 10:24
Just by simply copy and paste.
Excelchat Expert 13/06/2018 - 10:24
I will explain how to do it, its very simple
Excelchat Expert 13/06/2018 - 10:26
The formula that you need to put in A2 is = A1+1
Excelchat Expert 13/06/2018 - 10:27
and just drag it down till the last row that you need
Excelchat Expert 13/06/2018 - 10:27
This will fill out the date in sequence.
Excelchat Expert 13/06/2018 - 10:27
Now to hide the weekends, you will need to go to Conditional formatting from Home tab
Excelchat Expert 13/06/2018 - 10:28
Click on New rule
Excelchat Expert 13/06/2018 - 10:29
Under select rule Type, choose 'Use a custom formula' which is the last option
Excelchat Expert 13/06/2018 - 10:29
Under format values where this formula is true, paste this formula
Excelchat Expert 13/06/2018 - 10:29
=Or(weekday(a1)=1,weekday(a1)=7)
Excelchat Expert 13/06/2018 - 10:30
And then choose Format to say font as white
Excelchat Expert 13/06/2018 - 10:30
This will make the Sat & Sun invisible.
Excelchat Expert 13/06/2018 - 10:31
Then click OK.
Excelchat Expert 13/06/2018 - 10:31
Were you able to follow me?
User 13/06/2018 - 10:32
Yes Think I have got all that written down . That means I do not need to format a DATE or DATE VALUE
Excelchat Expert 13/06/2018 - 10:33
You will need to format the column to the desired date format like what I have done, but otherwise you do not need to do any Date or datevalue formula.
Excelchat Expert 13/06/2018 - 10:34
User 13/06/2018 - 10:35
Yes Understand that. format the whole column as in the date format for Day and time and then continue as you have done. Sounds too easy
Excelchat Expert 13/06/2018 - 10:36
That's correct.
Excelchat Expert 13/06/2018 - 10:36
User 13/06/2018 - 10:36
Thanks a lot if this works you will seem me again. Cheers Gordon.
Excelchat Expert 13/06/2018 - 10:36
Please do give me a good feedback for my service.
Excelchat Expert 13/06/2018 - 10:36
Thanks for your time. Please do come back for any new question. Have a great day ahead!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.