Need Explanation of This formula please

  • Thread starter Thread starter Dave Potter
  • Start date Start date
D

Dave Potter

Could someone explain how this formula works please. I tried
dissecting it using VBA help but I cant get it.

=IF(MOD(N4,7)=0,"Saturday",CHOOSE(MOD(N4,7),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"""))

Thanks,
Dave
 
Hi Dave,

The MOD function returns the modulus or remainder after a
division operation. The CHOOSE function returns the value
from a list of values based on an index number specified
in the function call. In this case that index number is
being generated by the MOD function nested inside of the
CHOOSE function. In plain English:

If N4 divided by 7 = 0 return Saturday, If N4 divided by 7
= 1 return Sunday
= 2 return Monday
...
...
=6 return Friday

Biff
 
I think I was complicating figuring this out. What threw me off was
this:
with the date 1/2/03 in N4 and the formula

=IF(MOD(N4,7)=0,"Saturday",CHOOSE(MOD(N4,7),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"))
in K3, I clicked inside the MOD function and hit the fx (insert
function). What it displayed was
Number: N4 = 37623
Divisor: 7 = 7
= 5

formula result = Thursday

I couldnt figure out how 37623 divided by 7 equals 5 but i understood
how 5 returns Thursday.

Could you explain that Division process?
Thanks,
Dave
 
Dave,

MOD(N4,7) is not dividing N4 by 7, but as Harlan said, it returns the
remainder after dividing the date number by 7.

Difference:-

108 /7 = 15.42857

MOD(108,7) = 3
 
Bob,
I dont want to show my stupidity here but...
for the life of me I cant figure this thing out and I know its silly
to belabour this but you know how some things just stick in your craw
till you figure them out? If you can help this poor boy figure this
out I promise not to post to this thread again ;)

I think the remainder thing is throwing me off. Could you take it step
by step so I can get some sleep tonight? (LOL)
 
Dave,

I will try, but I don't know what I can add that wasn't in Harlan's original
post.

As Harlan said, N4 contains a date. Although the date may look like say
07/06/03 (i.e. a date). in Excel it is actually the number of days since 1st
Jan 1900. So a cell with 09/08/03 actually stores the n umber 37,842.

This formula is then working out which day that is between 0 and 6 to start
to get a day name. So it takes the modulus 7 of that number using the MOD
function. MOD Returns the remainder after number is divided by divisor. So,
again using today's date, MOD(09/08/84,7) returns 0, as the 1st Jan 1900 was
a Saturday, the weekday number today is 0. Tomorrow will return 1, Monday
will return 2, and next Saturday will again return 0.

Knowing the day number, we can simply pass this to a CHOOSE statement looks
up that entry in an array of names
CHOOSE(MOD(N4,7),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"
"")). The IF statement is there as CHOOSE is 1-based, and MOD 7 returns 0-6,
so you have to cater for the 0 instance. Whoever wrote the code did this by
catering for 0 with the IF, but it could just as easily be done by
=CHOOSE(MOD(N4,7)+1,"Saturday","Sunday","Monday","Tuesday","Wednesday","Thur
sday","Friday")


But as Harlan says, it's all superfluous, because the formula
=TEXT(N4, "dddd")
does it better.

Hope that helps.
 
Hi Dave

Forget the dates and days for a little while.
Mod works this way: We have a number, say 45. We want to see how many ten's are in there:
=INT(45/10)
which makes 4, four tens. After removing those tenners then we have 5 left:
=MOD(45,10)
That's what mod do: removes all multiples of the spesified number ald leaves the rest as
result.

Ok, dates. Day 0 is saturday. Next day, 1, is sunday, 2 is monday, ... 6 is friday, 7 is
saturday, 8 is sunday,...

How do we know that day 8, 15, 22, ... are sundays ? We remove all groups of 7 and see
what's left; that's 1. So sunday it is.
 
Back
Top