Date function - need help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ann - 10 Mar 2008 23:38 GMT

Hello -

I have a column that has a date in it and I would like to create a function
that will display the following Wednesday's date in the next column.

Is this possible? If so, can someone please tell me how to write the
function?

Thanks in advance. Any help is greatly appreciated!

Reply to this Message

Jim - 10 Mar 2008 23:54 GMT

If your dates are in column A, try this in B1 and copy down:

=IF(WEEKDAY(A6,1)<4,4-WEEKDAY(A6,1)+A6,IF(WEEKDAY(A6,1)=4,7+A6,11-WEEKDAY(A6,1)+A6))

HTH,
Jim

> Hello -
>
[quoted text clipped - 5 lines]
>
> Thanks in advance. Any help is greatly appreciated!

Reply to this Message

Gary''s Student - 11 Mar 2008 00:00 GMT

With dates in column A:

=A1+CHOOSE(WEEKDAY(A1),3,2,1,7,6,5,4,3,2,1)

Signature

Gary''s Student - gsnu2007f

> Hello -
>
[quoted text clipped - 5 lines]
>
> Thanks in advance. Any help is greatly appreciated!

Reply to this Message

David Biddulph - 11 Mar 2008 00:05 GMT

=A1+MOD(11-WEEKDAY(A1),7)
I have assumed that if the date is a Wednesday you will show that date, not
a week hence?
--
David Biddulph

> Hello -
>
[quoted text clipped - 6 lines]
>
> Thanks in advance. Any help is greatly appreciated!

Reply to this Message

Ron Rosenfeld - 11 Mar 2008 02:33 GMT

>=A1+MOD(11-WEEKDAY(A1),7)
>I have assumed that if the date is a Wednesday you will show that date, not
>a week hence?
>--
>David Biddulph

WEEKDAY already does a MOD(n,7) function.

So if you make that assumption about NOT advancing a week if the date is a
Wednesday, simpler would be:

=A1+7-WEEKDAY(A1-4)

--ron

Reply to this Message

David Biddulph - 11 Mar 2008 09:05 GMT

Yes, that makes sense. I thought there must be an easier way. :-)
--
David Biddulph

> WEEKDAY already does a MOD(n,7) function.
>
> So if you make that assumption about NOT advancing a week if the date is a
> Wednesday, simpler would be:
>
> =A1+7-WEEKDAY(A1-4)

>>=A1+MOD(11-WEEKDAY(A1),7)
>>I have assumed that if the date is a Wednesday you will show that date,
>>not
>>a week hence?
>>--
>>David Biddulph

Reply to this Message

Ron Rosenfeld - 11 Mar 2008 01:16 GMT

>Hello -
>
[quoted text clipped - 5 lines]
>
>Thanks in advance. Any help is greatly appreciated!

=A1+8-WEEKDAY(A1+4)

--ron

Reply to this Message

pai - 11 Mar 2008 19:15 GMT

> Hello -
>
[quoted text clipped - 5 lines]
>
> Thanks in advance. Any help is greatly appreciated!

I am not sure what u want let take this example

A1 =11/march/2008 and if you want Tuesday in B1

Then The formula is =A1

Select the column
Go to Format
Then Cells
Numbers
Customs
Type DDD

Then Ok

Hopefully this will help You

Cheers
Hardeep Kanwar

Reply to this Message