Excel - Monday/ Previous Week Validation 4

[Guest_imported]

I need to validate a cell so that only the date of the Monday of the previous week will be accepted.

I have Monday validated by WEEKDAY, but am having difficulty restricting the date.

Any help would be appreciated.

With Thanks,

Maureen

 

[Dreamboat]

Mo, I didn't get a chance to review it, but try here:

http://www.cpearson.com/excel/datetime.htm#NthDoW

techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[Guest_imported]

What service! <g>

Many thanks for the link. I've had a very quick look and it appears a mine of Excel information.

I'll let you know if I find the answer in there.

With thanks and all best wishes,

Mo

 

[Dreamboat]

Ok. You like that one? You'll like these too, but perhaps not QUITE as much:

http://www.erlandsendata.no/english/downloads/index.htm

http://www.excel-vba-access.com/jobs-excel-vba.htm

http://www.turedata.se

http://s9000.furman.edu/DD/labs/Excel/

(for your friends)

techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[Guest_imported]

Thanks again.

I'll go browsing, but it'll have to be tomorrow/later today ... It's 2.30 a.m. here and bed is calling!

&quot;The early bird may get the worm, but the second mouse gets the cheese .....
night owls get neither, but meet good folk on the Internet.&quot; <g>

Mo

 

[Dreamboat]

Hmmmm. Only 9:30 pm here!
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[DarkSun]

In validation select Custom then enter this Formula:

=A1=TODAY()-(WEEKDAY(DAY(TODAY()))+5)

Where A1 is the cell you are applying the validation to.

I have tested this a bit but let me know if you have any problems.

 

[Dreamboat]

Hey, DarkSun, while you're here, then:

I'm thinking maybe Maureen would like that date to come up automatically. I mean, why make sure a certain date is entered when you can just have it be entered automatically, right?

DarkSun, do you know how to do that?
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[DarkSun]

Yeah my mate pointed that out at Lunch time:

just put this in the cell:

=TODAY()-(WEEKDAY(DAY(TODAY()))+5)

 

[Guest_imported]

Thanks for your help.

Unfortunately both arguements are accepting any/all dates.

To have it put in the right date automatically makes sense, but it's an assignment exercise!!! I have to provide a specific warning error alert, which limits me to using Data Validation.

My present validation, which is working to restrict to Mondays is =WEEKDAY(B3)=2

If my logic is correct I have also to limit it to between -7 and -13 days from the input date, i.e. not Monday of the current week, but the Monday of the previous week. I've tried writing this in every way I can think of, but I'm not getting the arguement right. Any clues?

Please/Thank you ...

Maureen

 

[DarkSun]

As far as my testing goes this works fine in Validation:

=A1=TODAY()-(WEEKDAY(DAY(TODAY()))+5)

How did you put this in Validation?? What cell did you validate?

 

[DarkSun]

You can shorten My Formula to this:

=A1=TODAY()-(WEEKDAY(TODAY())+5)

I will explain how this Formula works.

1. Finds the weekday of Today (5)
2. This number will be Subtracted from Today to give Last Saturday.
3. Then subtracts another 5 days to get to Monday.

I hope this helps

 

[Guest_imported]

I copied exactly(Quadruple checked! <g>), into B3, which is the cell I'm trying to validate, using Data/Validation/Custom ...

Maureen

 

[Guest_imported]

I copied exactly(Quadruple checked! <g>), into B3, which is the cell I'm trying to validate, using Data/Validation/Custom ...

Maureen

 

[DarkSun]

Copy this into Validation on B3:

=B3=TODAY()-(WEEKDAY(TODAY())+5)

 

[Guest_imported]

Thanks a million ... it's working. Don't know what I did wrong before - sure to be my error - sorry!

If you folks were over here (UK) you'd be on champagne. <g>

Maureen

PS sorry about the delays, my ISP is hiccuping

 

[Guest_imported]

Sorry to be a nuisance, but I can't get back to my other thread ...

I have a cell set up to select the MIN result of three cells and display the heading of the column containing the lowest figure.

=IF(MIN(B1717)=B17, B6, IF(MIN(B1717)=C17, C6, IF(MIN(B1717)=D17, D6)))

My problem is how to display all the results when more than one cell contains the smallest figure.

Please can you tell me if anyone has picked up on it, or please can you help?

Mo

 

[Guest_imported]

P.S. I forgot to say thanks for the explanation ... that was very much appreciated, saving me time fathoming how the formula worked.

I always have to know the how and why ... just ask my long-suffering husband!!! <g>

Mo

 

[DarkSun]

One way to do it is to do something like this:

=IF($B$17=MIN($B$17:$D$17),$B$6,&quot;&quot & &quot; &quot; & IF($C$17=MIN($B$17:$D$17),$C$6,&quot;&quot & &quot; &quot; & IF($D$17=MIN($B$17:$D$17),$D$6,&quot;&quot

 

[DarkSun]

Use this one instead:

=IF($B$17=MIN($B$17:$D$17),$B$6 & &quot; &quot;,&quot;&quot & IF($C$17=MIN($B$17:$D$17),$C$6 & &quot; &quot;,&quot;&quot & IF($D$17=MIN($B$17:$D$17),$D$6,&quot;&quot

It positions the spaces better.