[The Rook[_2_]]

I have a list of dates in column A and would like to populate column B with
either 1 or Y is the date is a sat or a sun.

[Mike H]

Try this

=IF(WEEKDAY(A1)=1,"Y","")

Drag down

Mike

"The Rook" wrote:

I have a list of dates in column A and would like to populate column B with
either 1 or Y is the date is a sat or a sun.

[Don Guillett]

try
=IF(OR(WEEKDAY(a2)={1,7}),1,2)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"The Rook" wrote in message
...
I have a list of dates in column A and would like to populate column B with
either 1 or Y is the date is a sat or a sun.

[The Rook[_2_]]

Thanks for that, but how do I pick up saturdays?

Regards


"Mike H" wrote:

Try this

=IF(WEEKDAY(A1)=1,"Y","")

Drag down

Mike

"The Rook" wrote:

I have a list of dates in column A and would like to populate column B with
either 1 or Y is the date is a sat or a sun.

[Rick Rothstein \(MVP - VB\)[_660_]]

Give this a try...

=IF(WEEKDAY(A1,3)4,"Y","")

Rick


"The Rook" wrote in message
...
I have a list of dates in column A and would like to populate column B with
either 1 or Y is the date is a sat or a sun.

[Rick Rothstein \(MVP - VB\)[_661_]]

More compact, but much less obvious<g...

=IF(MOD(A1,7)<2,"Y","")

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Give this a try...

=IF(WEEKDAY(A1,3)4,"Y","")

Rick


"The Rook" wrote in message
...
I have a list of dates in column A and would like to populate column B
with
either 1 or Y is the date is a sat or a sun.

[Dave Peterson]

One mo
=IF(WEEKDAY(A1,2)5,"Y","")

And another:
=IF(WEEKDAY(A1,2)<6,"","Y")



The Rook wrote:

I have a list of dates in column A and would like to populate column B with
either 1 or Y is the date is a sat or a sun.

--

Dave Peterson

[Dave Peterson]

This will have problems if the 1904 date system is being used (Mac users and
weird Wintel users <vbg).

"Rick Rothstein (MVP - VB)" wrote:

More compact, but much less obvious<g...

=IF(MOD(A1,7)<2,"Y","")

Rick

"Rick Rothstein (MVP - VB)" wrote in
message ...
Give this a try...

=IF(WEEKDAY(A1,3)4,"Y","")

Rick


"The Rook" wrote in message
...
I have a list of dates in column A and would like to populate column B
with
either 1 or Y is the date is a sat or a sun.


--

Dave Peterson

[Rick Rothstein \(MVP - VB\)[_663_]]

I was under the impression that the only difference between the "normal"
Excel date system and the 1904 date system was in the first 4 years (because
the "normal" system improperly considered 1900 as a non-leap year) and,
after that, they were in complete agreement; and, from 1904 on, the VB
serial dates matched the Excel worksheet serial dates. I was also under the
impression that the underlying serial date calculations for Mac and PC were
both the same. If that is the case, then the MOD formula should work (from
1904 onward). Am I wrong in my understandings of this?

Rick


"Dave Peterson" wrote in message
...
This will have problems if the 1904 date system is being used (Mac users
and
weird Wintel users <vbg).

"Rick Rothstein (MVP - VB)" wrote:

More compact, but much less obvious<g...

=IF(MOD(A1,7)<2,"Y","")

Rick

"Rick Rothstein (MVP - VB)" wrote
in
message ...
Give this a try...

=IF(WEEKDAY(A1,3)4,"Y","")

Rick


"The Rook" wrote in message
...
I have a list of dates in column A and would like to populate column B
with
either 1 or Y is the date is a sat or a sun.


--

Dave Peterson

[David Biddulph[_2_]]

Today (11 Jun 08) is 39610 in the 1900 system, and 38148 in the 1904 system.
MOD(...,7) gives 4 and 5 respectively.
The correction term between the 2 systems is 1462, and MOD(1462,7) gives 6.
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
I was under the impression that the only difference between the "normal"
Excel date system and the 1904 date system was in the first 4 years
(because the "normal" system improperly considered 1900 as a non-leap year)
and, after that, they were in complete agreement; and, from 1904 on, the VB
serial dates matched the Excel worksheet serial dates. I was also under the
impression that the underlying serial date calculations for Mac and PC were
both the same. If that is the case, then the MOD formula should work (from
1904 onward). Am I wrong in my understandings of this?

Rick


"Dave Peterson" wrote in message
...
This will have problems if the 1904 date system is being used (Mac users
and
weird Wintel users <vbg).

"Rick Rothstein (MVP - VB)" wrote:

More compact, but much less obvious<g...

=IF(MOD(A1,7)<2,"Y","")

Rick

"Rick Rothstein (MVP - VB)" wrote
in
message ...
Give this a try...

=IF(WEEKDAY(A1,3)4,"Y","")

Rick


"The Rook" wrote in message
...
I have a list of dates in column A and would like to populate column B
with
either 1 or Y is the date is a sat or a sun.


--

Dave Peterson

[Dave Peterson]

I think so.

If you type 1 into a cell and you're using the 1900 date system, format it as a
date, you'll see:
01/01/1900 (a Sunday)

Change to the 1904 date system and the value of the cell is still one, but
you'll see:
01/02/1904 (a Saturday)

And that's enough to screw up the mod() stuff.


"Rick Rothstein (MVP - VB)" wrote:

I was under the impression that the only difference between the "normal"
Excel date system and the 1904 date system was in the first 4 years (because
the "normal" system improperly considered 1900 as a non-leap year) and,
after that, they were in complete agreement; and, from 1904 on, the VB
serial dates matched the Excel worksheet serial dates. I was also under the
impression that the underlying serial date calculations for Mac and PC were
both the same. If that is the case, then the MOD formula should work (from
1904 onward). Am I wrong in my understandings of this?

Rick

"Dave Peterson" wrote in message
...
This will have problems if the 1904 date system is being used (Mac users
and
weird Wintel users <vbg).

"Rick Rothstein (MVP - VB)" wrote:

More compact, but much less obvious<g...

=IF(MOD(A1,7)<2,"Y","")

Rick

"Rick Rothstein (MVP - VB)" wrote
in
message ...
Give this a try...

=IF(WEEKDAY(A1,3)4,"Y","")

Rick


"The Rook" wrote in message
...
I have a list of dates in column A and would like to populate column B
with
either 1 or Y is the date is a sat or a sun.


--

Dave Peterson

--

Dave Peterson

[Rick Rothstein \(MVP - VB\)[_665_]]

I guess it shows that I have never used the 1904 system.<g I see now... the
values for "date zero" in the two systems differ. Thanks for pointing this
out for me.

Rick



"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Today (11 Jun 08) is 39610 in the 1900 system, and 38148 in the 1904
system. MOD(...,7) gives 4 and 5 respectively.
The correction term between the 2 systems is 1462, and MOD(1462,7) gives
6.
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
I was under the impression that the only difference between the "normal"
Excel date system and the 1904 date system was in the first 4 years
(because the "normal" system improperly considered 1900 as a non-leap
year) and, after that, they were in complete agreement; and, from 1904 on,
the VB serial dates matched the Excel worksheet serial dates. I was also
under the impression that the underlying serial date calculations for Mac
and PC were both the same. If that is the case, then the MOD formula
should work (from 1904 onward). Am I wrong in my understandings of this?

Rick


"Dave Peterson" wrote in message
...
This will have problems if the 1904 date system is being used (Mac users
and
weird Wintel users <vbg).

"Rick Rothstein (MVP - VB)" wrote:

More compact, but much less obvious<g...

=IF(MOD(A1,7)<2,"Y","")

Rick

"Rick Rothstein (MVP - VB)" wrote
in
message ...
Give this a try...

=IF(WEEKDAY(A1,3)4,"Y","")

Rick


"The Rook" wrote in message
...
I have a list of dates in column A and would like to populate column
B
with
either 1 or Y is the date is a sat or a sun.


--

Dave Peterson

[Rick Rothstein \(MVP - VB\)[_666_]]

Yes, I saw David's post and now I understand that I didn't understand
before.<g

We could patch the MOD version like this...

=IF(MOD(A1-(DATE(1904,1,1)=0),7)<2,"Y","")

and I think it will work correctly in both systems... but, of course, it is
no longer more compact than the WEEKDAY version and, as such, not as
interesting any more.

Rick


"Dave Peterson" wrote in message
...
I think so.

If you type 1 into a cell and you're using the 1900 date system, format it
as a
date, you'll see:
01/01/1900 (a Sunday)

Change to the 1904 date system and the value of the cell is still one, but
you'll see:
01/02/1904 (a Saturday)

And that's enough to screw up the mod() stuff.


"Rick Rothstein (MVP - VB)" wrote:

I was under the impression that the only difference between the "normal"
Excel date system and the 1904 date system was in the first 4 years
(because
the "normal" system improperly considered 1900 as a non-leap year) and,
after that, they were in complete agreement; and, from 1904 on, the VB
serial dates matched the Excel worksheet serial dates. I was also under
the
impression that the underlying serial date calculations for Mac and PC
were
both the same. If that is the case, then the MOD formula should work
(from
1904 onward). Am I wrong in my understandings of this?

Rick

"Dave Peterson" wrote in message
...
This will have problems if the 1904 date system is being used (Mac
users
and
weird Wintel users <vbg).

"Rick Rothstein (MVP - VB)" wrote:

More compact, but much less obvious<g...

=IF(MOD(A1,7)<2,"Y","")

Rick

"Rick Rothstein (MVP - VB)"
wrote
in
message ...
Give this a try...

=IF(WEEKDAY(A1,3)4,"Y","")

Rick


"The Rook" wrote in message
...
I have a list of dates in column A and would like to populate column
B
with
either 1 or Y is the date is a sat or a sun.


--

Dave Peterson

--

Dave Peterson