The formula below is to create a month to view calendar. And I was wondering if someone here could explain this formula to me by breaking it down in parts so I can understand it.
=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))
<>MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-
(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+
{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"",DATE(YEAR(NOW()),
MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),
MONTH(NOW()),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7} -1
Thank you.
OK, start with this part
=DATE(YEAR(NOW()),MONTH(NOW()),1)
That gives you the 1st of the current month and then, a fairly well known trick, to find the last Saturday of the previous month you can use this:
=DATE(YEAR(NOW()),MONTH(NOW()),1)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))
....but the formula you quoted is like this
=DATE(YEAR(NOW()),MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)
thereby subtracting -1, i.e. adding 1......to get the Sunday date within the 7 days ending on 1st current month....
.....now you add this:
{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1
which creates a "matrix" of dates, with each column adding one day [{1,2,3,4,5,6,7}] and each row adding 7 days [{0;1;2;3;4;5}*7]
so if you enter this part of the formula alone....
=DATE(YEAR(NOW()),MONTH(NOW()),1)-
(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+
{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1
.....in a 6 row x 7 column range, you'll get 42 consecutive dates starting with that Sunday I mentioned and going right through to next month.......but of course you don't want to display any dates that don't fall in the current month so the whole formula just uses an IF to check the month, so if the above is "Formula1" then we do this
=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))<>MONTH(formula1),"",formula1)
that checks whether each of the 42 dates is in the current month, if not it displays a blank, if yes then the date is displayed, so you get the dates in the correct layout, with each week starting on a Sunday (you can adjust the formula for any weekday start). Normally I assume the cells would be custom formatted as d to show just the day but remember the underlying value is a date.
Note the red part is overkill because
=MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))
is the same as
=MONTH(NOW())
....and more generally you can replace DATE(YEAR(NOW()),MONTH(NOW()),1) whenever it occurs with TODAY()-DAY(TODAY())+1, so with a few other simplifications this formula will do the same thing
=IF(MONTH(TODAY()-DAY(TODAY())+1-WEEKDAY(TODAY()-DAY(TODAY())+1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7})<>MONTH(TODAY()),"",TODAY()-DAY(TODAY())+1-WEEKDAY(TODAY()-DAY(TODAY())+1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7})
or to simplify even further put today's date in T1 and use this version
=IF(MONTH($T$1-DAY($T$1)+1-WEEKDAY($T$1-DAY($T$1)+1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7})<>MONTH($T$1),"",$T$1-DAY($T$1)+1-WEEKDAY($T$1-DAY($T$1)+1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7})
To adjust the start day (the day of the left hand column) to Monday remove the 2 blue +1s, to make the start day Saturday change to +2 etc.
Last edited by daddylonglegs; 08-21-2012 at 06:59 PM.
Audere est facere
Thank you for your explanation.
I'm now digesting all that and will get back to you shortly either to confirm the matter is solved or to ask more questions.
Thank you again.
Hi Daddy, I'm still having indigestion with this formula.
This part of the formula
=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))
gives me the MONTH of the date in brackets, i.e., MONTH 8;
However, if not
<>MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-
(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+
{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)
the month less the weekday of the dates in brackets (again), whose result is 7, then returns a blank, otherwise the formula gives the outcome of
DATE(YEAR(NOW()),MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1
which is the first date of the current month less the three days from the previous month that went into the first week of the current month.
Is that the correct reading so far (or rather a way to look at it?)
Now, what about those overlapping days at the end of the month? How did they get taken out in the end?
Could you also explain to me please what is the -1 at the end of the matrix formula below
+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7} -1
Thanks.
Anyone willing to help me, please feel free to post.
Thanks.
Ah, somehow I missed your last posts - let me look at that and get back to you.......
Please get back to me on this formula when you can.
Thank you.
Hi
I'm deucedly sorry for asking you to go over the explanation for that formula again.
If you can, please, can you try to break it down in bit sizes, so I can understand its internal workings - please.
I know you really put a lot of effort into your first explanation, and I am truly grateful for that.
Please take a look at my last comment
Thank you.This part of the formula
=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))
gives me the MONTH of the date in brackets, i.e., MONTH 8;
However, if not
<>MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-
(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+
{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)
the month less the weekday of the dates in brackets (again), whose result is 7, then returns a blank, otherwise the formula gives the outcome of
DATE(YEAR(NOW()),MONTH(NOW()),1)-(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1
which is the first date of the current month less the three days from the previous month that went into the first week of the current month.
Is that the correct reading so far (or rather a way to look at it?)
Now, what about those overlapping days at the end of the month? How did they get taken out in the end?
Could you also explain to me please what is the -1 at the end of the matrix formula below
+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7} -1
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks