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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cainart - 11 Jul 2007 14:42 GMT

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?

Reply to this Message

Roger Govier - 11 Jul 2007 16:06 GMT

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

Signature

Regards

Roger Govier

>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
[quoted text clipped - 10 lines]
> put the
> start date and end date at the top of the sheet?

Reply to this Message

cainart - 11 Jul 2007 20:32 GMT

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
>
[quoted text clipped - 21 lines]
> > put the
> > start date and end date at the top of the sheet?

Reply to this Message

Gord Dibben - 12 Jul 2007 00:23 GMT

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

>Hello
>I think I need a little more information, thank you. It looks like you want
[quoted text clipped - 30 lines]
>> > put the
>> > start date and end date at the top of the sheet?

Reply to this Message

cainart - 12 Jul 2007 00:40 GMT

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

> Hello
> I think I need a little more information, thank you. It looks like you want
[quoted text clipped - 30 lines]
> > > put the
> > > start date and end date at the top of the sheet?

Reply to this Message

Gord Dibben - 12 Jul 2007 01:30 GMT

Post the URL where I can download your Template and see what's up.

Should be an easy fix.

Gord

>Hi again
>I did what you said and it isn't working, however I was wondering if I need
[quoted text clipped - 37 lines]
>> > > put the
>> > > start date and end date at the top of the sheet?

Reply to this Message

cainart - 12 Jul 2007 03:40 GMT

Hello
I got the be-weekly template from
http://office.microsoft.com/en-ca/templates/TC010709421033.aspx?CategoryID=CT101
172771033

Version: Excel 2000 or later
Download size: 20 KB (<1 min @ 56 Kbps)

I hope this helps you to help me and I will try what you already suggested
as well.
Thanks

> Post the URL where I can download your Template and see what's up.
>
[quoted text clipped - 43 lines]
> >> > > put the
> >> > > start date and end date at the top of the sheet?

Reply to this Message

Gord Dibben - 12 Jul 2007 04:09 GMT

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 got the be-weekly template from
[quoted text clipped - 54 lines]
>> >> > > put the
>> >> > > start date and end date at the top of the sheet?

Reply to this Message

cainart - 12 Jul 2007 13:10 GMT

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

> Select A15 and type in Thursday.
>
[quoted text clipped - 66 lines]
> >> >> > > put the
> >> >> > > start date and end date at the top of the sheet?

Reply to this Message

Gord Dibben - 12 Jul 2007 16:05 GMT

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

>Hello
>I did everything exactly how you told me to do at least to the best of my
[quoted text clipped - 76 lines]
>> >> >> > > put the
>> >> >> > > start date and end date at the top of the sheet?

Reply to this Message

Gord Dibben - 15 Jul 2007 00:06 GMT

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

Gord

>Email me and I will send you an altered version of the Template.
>
[quoted text clipped - 82 lines]
>>> >> >> > > put the
>>> >> >> > > start date and end date at the top of the sheet?

Reply to this Message

cainart - 25 Jul 2007 12:50 GMT

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

> Received your email, made the changes to the Template, but have tried 3 times to
> send to you but keep getting "undeliverable" notices.
[quoted text clipped - 87 lines]
> >>> >> >> > > put the
> >>> >> >> > > start date and end date at the top of the sheet?

Reply to this Message

cainart - 11 Jul 2007 20:40 GMT

One more thing I think need a code for thursday, the dddd is for sunday.

> Hi
>
[quoted text clipped - 21 lines]
> > put the
> > start date and end date at the top of the sheet?

Reply to this Message

Roger Govier - 11 Jul 2007 23:05 GMT

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?

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.

Signature

Regards

Roger Govier

> One more thing I think need a code for thursday, the dddd is for
> sunday.
[quoted text clipped - 29 lines]
>> > put the
>> > start date and end date at the top of the sheet?

Reply to this Message

Rate this thread: