Weekend

[The Rook]

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

 

[Don Guillett]

try
=IF(OR(WEEKDAY(a2)={1,7}),1,2)

 

[The Rook]

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

Regards

 

[Rick Rothstein \(MVP - VB\)]

Give this a try...

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

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

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

Rick

 

[Dave Peterson]

One more:
=IF(WEEKDAY(A1,2)>5,"Y","")

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

 

[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\)]

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

 

[David Biddulph]

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

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]

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\)]

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

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.

 

[Rick Rothstein \(MVP - VB\)]

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