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
Does that solve your query?
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
Is there anything else I can help you with this query?
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.
