Excel - Limit Date Entered to Fridays Only? 3

[BlaineR]

Hello,

In Excel, what would be the easiest/best way to ensure that users only enter a date if it corresponds to a Friday?

I'm making a weekly timesheet. Users are supposed to indicate the week by entering the Friday date for that week. So, I want to prevent the entry of any date that is not a Friday.

I am aware of the WEEKDAY function, but I can't seem to find a way to use it in data validation.

Any thoughts? Thanks!

 

[onedtent]

=NOW()+(6-WEEKDAY(NOW()))

 

[BlaineR]

Thanks for the quick reply, onedtent. Unless I'm mistaken, you are suggesting that I put this formula in the cell itself. However, I don't want to autopopulate the cell, since users may be completing a timesheet for a previous week or even several weeks back (or even in the future).

I need to let them enter a value which I then validate as being a Friday. Or were you suggesting something else that I'm missing?

Thanks again.

 

[mscallisto]

Yes BlaineR you can do what you asked for, have someone enter a date then tell them the date is not a friday and ask them to re-enter.

Another option is to automatically adjust the date to the friday of that week.

Which do you prefer?

 

[BlaineR]

Hi mscallisto.

Good question. To avoid deceiving the user, I prefer requiring them to re-try until they enter a Friday. (That is, the same general idea as if you want to validate that an entry is, say, >350 -- if they enter "321", they get an error message saying that they can re-try or cancel.)

In my case, if they enter "03/13/03" (i.e. a Thursday) they would get the error message but if they enter "03/14/03" (i.e. a Friday) the entry would be accepted.

Thanks!

 

[DaleWatson123321]

Blaine,

Steps to use for Data Validation - which will allow for ANY Friday...

1) Under Settings - Allow, choose "Custom"

2) Under Formula, enter this formula:
=MOD(xxx,7)=6 ...where xxx is the current cell

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[BlaineR]

Dale, you are a genius. This is EXACTLY what I wanted to do.

Thanks so much!

Blaine

 

[mscallisto]

Good God you Canadians are fast!!!.

Dosen't the cold would slow you down at all?

Nice answer Dale.