Excel Bi-weekly time sheet change dates to Thursday- Wednesday?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I downloaded a Bi-weekly timesheet for Excel. The timesheet is perfect
except for the date part. The time sheet comes with a Monday to Friday
bi-weekly time set-up and I need to change it to a Thursday to Wednesday
bi-weekly time set-up. At present I had to individually change the days
starting with the Thursday and when I change the start date and ending dates
the only days that automatically change are the first Thursday to Sunday
settings (4 days). How do I set it up so that all days change when I put the
start date and end date at the top of the sheet?
 
Hi

Starting with the cell representing the first day - let's assume this is
cell A5, enter
=A1 where A1 represents the cell location where you have entered start
date.

In A6 enter =
A5+1 and copy down.
Format the cells, Format>Cell>Number>Custom> dddd
 
Hello
I think I need a little more information, thank you. It looks like you want
me to put in this case it is A15 for the start date and instead of Thursday
you want me to put in A14+1 and copy down (not sure what you mean). Please
understand I have never formatted anything before so I need detailed info to
accomplish this. It sounds like you can help if you have the patience.
I look forward to your response, thank you.
cainart
 
Hi

Formatting a cell as dddd will show the day of the week of the date
entered in that cell.
If the date entered was a Sunday date, it will show Sunday. If the date
is a Thursday date e.g. 11 Jul 2007, then it will show Thursday.
How do I set it up so that all days change when I put the start date
and end date at the top of the sheet?
Click to expand...
From your original posting, it sounded as through you were entering the
start date for the period in a cell at the top of the sheet.
What I was saying, was you need to create a reference to that cell, in
the cell where you want Thursday to appear.
If that happens to be cell A15, then in A15 enter
=A1 or whatever is the cell reference where you have entered your
start date.

In cell A16 enter
=A15+1 this will set the date to be one day later than the start date,
and when formatted as dddd, it will show Friday

If you then copy this formula down column A for the next 12 cells, the
formula will alter to A16+1, A17+1 etc. and the dates will step up
accordingly, and thus the actual day shown in the cell will step up to
Saturday, Sunday, Monday etc.
 
The Monday to Sunday in Column A of that template is hard-coded weekdays...no
formulas.

Just change Monday to Thursday and copy down. The days will change.

The dates in column B are formula driven.

Just change the date in Start Date at K6.

End Date is formula driven from Start Date so no need to touch it.


Gord Dibben MS Excel MVP
 
Hi again
I did what you said and it isn't working, however I was wondering if I need
to something didfferent since the start of the 2wk period is put into D12 and
the end of the 2wk period is put into D13. Could you try to help me one more
time?
Thank you
 
Post the URL where I can download your Template and see what's up.

Should be an easy fix.


Gord
 
Select A15 and type in Thursday.

Drag/copy that down to A28

Select D11 and change the date to 7/12/2007

Don't touch D12...........it will update with D11

Note the dates in B15:B28 will change for the 14-day period.


Gord
 
Hello
I did everything exactly how you told me to do at least to the best of my
knowledge and nothing in the A column changed when I drag/copy, it just
starts with Thursday and then Tuesday. The date only changes on the first 3
days and the D12 spot doesn't change.
I guess I should just go back to changing the dates one at a time and save
myself the frustration. Obvilusly what should be a simple fix just isn't
co-operating with me.
Thanks
 
Email me and I will send you an altered version of the Template.

Change the DOT and AT to appropriate punctuation to get my email address.


Gord
 
Received your email, made the changes to the Template, but have tried 3 times to
send to you but keep getting "undeliverable" notices.

Gord
 
Hello again Gord
I just wanted to let you know that I figured out how to change the date
formulas. You missed telling me one thing, that when i click on the dates a
formula shows up on the formula bar and that is where I was suppose to change
things. So I did what you suggested in the formula bar and everything works
great, thanks.
Robin
 
Back
Top