Prev: how can I use randbetween without repeating numbers in a set
Next: Row to Column
From: jatman on 31 Oct 2009 15:21
i want to validate the date entered as a Sunday - if the user enters a date
that is not a Sunday, the user cannot continue.

cell A1 - user enters the date (the cell is formatted as custom yyyy-mm-dd)
cell A2 - text(a1,"dddd") returns back the day of the week

is there a way to validate the value as a Sunday date.

jat

From: T. Valko on 31 Oct 2009 16:55
Select cell A1
Goto the menu Data>Validation
Allow: Custom
Formula: =WEEKDAY(A1)=1

You can use custom input or error alert messages if you want.

OK out

--
Biff
Microsoft Excel MVP


"jatman" <jatman(a)discussions.microsoft.com> wrote in message
news:2B7D98FC-DB04-4C03-B48E-9DA95F2B2251(a)microsoft.com...
>i want to validate the date entered as a Sunday - if the user enters a date
> that is not a Sunday, the user cannot continue.
>
> cell A1 - user enters the date (the cell is formatted as custom
> yyyy-mm-dd)
> cell A2 - text(a1,"dddd") returns back the day of the week
>
> is there a way to validate the value as a Sunday date.
>
> jat
>


From: Rick Rothstein on 31 Oct 2009 16:58
Not sure how you want to do the validation, but this will return TRUE if the
date in A1 is a Sunday and FALSE otherwise...

=WEEKDAY(A1)=1

So, you could do some variation on this for your validation formula...

=IF(WEEKDAY(A1)=1,"That date is a Sunday","Sorry, it's not a Sunday")

--
Rick (MVP - Excel)


"jatman" <jatman(a)discussions.microsoft.com> wrote in message
news:2B7D98FC-DB04-4C03-B48E-9DA95F2B2251(a)microsoft.com...
>i want to validate the date entered as a Sunday - if the user enters a date
> that is not a Sunday, the user cannot continue.
>
> cell A1 - user enters the date (the cell is formatted as custom
> yyyy-mm-dd)
> cell A2 - text(a1,"dddd") returns back the day of the week
>
> is there a way to validate the value as a Sunday date.
>
> jat
>

From: Tom Hutchins on 31 Oct 2009 17:07
Add Data Validation to A1. Select Data (ribbon tab in XL2007 or menu bar in
earlier versions) >> Data Validation >> set Allow to Custom >> for Formula
enter
=WEEKDAY(A1)=1) >> on the Error Alert tab, set the message to display if
a non-Sunday date is entered >> OK.

Hope this helps,

Hutch

"jatman" wrote:

> i want to validate the date entered as a Sunday - if the user enters a date
> that is not a Sunday, the user cannot continue.
>
> cell A1 - user enters the date (the cell is formatted as custom yyyy-mm-dd)
> cell A2 - text(a1,"dddd") returns back the day of the week
>
> is there a way to validate the value as a Sunday date.
>
> jat
>
 | 
Pages: 1
Prev: how can I use randbetween without repeating numbers in a set
Next: Row to Column