Counting the days and formatiing dropdowns.
Posted by Michael Woodhouse on January 11, 2001 4:48 AM
Here are the two biggest problems I've run into in my latest project:
I need to be able to subtract or add a number of days from a day (eg. Fri) and have it return a day (eg Fri). So what I want is Fri-7=Fri or Mon+17=Thu. I found a solution to it, but it's not flexible enough to move around. It took quite a few calculations to come up with.
The other one is formatting dropdown boxes. I had a couple of dropdown boxes and wanted to make the text inside them bigger, but all I could get was the actual box to grow. Am I missing something?
I also found it difficult (although I found a way) to make the cell I wanted to produce the option that was selected in the dropdown.
If the answer or a notice that the answer is posted could be emailed to me at mykol@home.com that would be great.
Posted by Mark W. on January 11, 2001 5:06 AM
If cell A1 contains "Mon" and cell B1 contains 17
then use:
=CHOOSE(MOD(ABS(B1),7)+MATCH(A1,{"Sun","Mon","Tue","Wed","Thu","Fri"},0),"Sun","Mon","Tue","Wed","Thu","Fri")
Posted by Mark W. on January 11, 2001 5:22 AM
If you find my formula (below) to be too "messy"
you could define the name "days" as:
={"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}
...and use
=INDEX(days,(MOD(ABS(B1),7)+MATCH(A1,days,0)))
Posted by Mark W. on January 11, 2001 5:23 AM
Oops, forgot Saturday!
=CHOOSE(MOD(ABS(B1),7)+MATCH(A1,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
Posted by Mark W. on January 11, 2001 6:27 AM
Please Ignore ALL My Postings Below
I can now appreciate the problems you faced with
the creation of a "simple" solution. I found a
defect that I hadn't adequately tested for...
I'm back at my "drawing board".
Posted by Mark W. on January 11, 2001 7:33 AM
FINALLY, I'm Satisfied!!!!
=TEXT(MATCH(A1,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)+MOD(ABS(B1),7),"ddd")
where A1 contains your day (e.g., "Fri")
and B1 contains your offset (in days).
Did you know 3000 days from Saturday is a Wednesday?
Posted by Mark W. on January 11, 2001 7:44 AM
One More Tweak!!
Should have removed that pesky ABS() function. Was
using it during testing. Use:
=TEXT(MATCH(A1,{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},0)+MOD(B1,7),"ddd")
where A1 contains your day (e.g., "Fri")
and B1 contains your offset (in days).
