Weekends/Bank Holidays Calc

[sullyman]

I'd like to change a field colour with conditional formatting if possible by using a calc on Date Fields that will flag weekends (Sat/Sun) and Bank Holidays as Colour Red etc.

Is this possible?

Thanks

[Søren Dyhr]

Well this does it, but something easier might do it for the weekday??:

GetAsBoolean(Filter(DayOfWeek ( Self );"17"))

Similar could be done with FilterValues for the Int( of the date, for bank holidays, but when it comes to easter and such will I suggest you ask Comment, who is an expert on date-issues ... whether or not it goes with this as well should go unsaid:

http://www.ovalbooks.com/bluff/Relationships.html

--sd

[sullyman]

Hi Soren,

How are you.

See Thread - http://fmforums.com/forum/showtopic.php?tid/192750/

This is really what i am looking for. Do you know the answer to what i asked in the thread link above by any chance.

Thanks,

Sully

[comment]

You could also try:

Mod ( self - 1 ; 7 ) > 4

But for holidays you will most likely need a list of them.

[comment]

Please try keeping to one thread per question.

[sullyman]

Sorry Comment, I will keep to the other thread

[Søren Dyhr]

Please note that conditional formatting has a switch'ish flow, so there is no need to compile two criteria in one single calc', and especially when two different colours are required.

--sd

[comment]

Do note that the other thread is about MODIFYING a date, so that a weekend/holiday date is shifted forward to a work date - not about formatting.

[Søren Dyhr]

But for holidays you will most likely need a list of them

Moveable feasts, shouldn't be plummeting in a Y2K shortcut should it : ??

--sd

[sullyman]

Sorry guys, i was going to flag the weekend dates etc. in colour so users could then see them immediately so they would know to change them to working dates.

Then i came across Comment's NextWorkday function which would suit better if i can get working.

[Søren Dyhr]

Well why??? as Todd Geis says:

Sometimes I think FileMaker is too flexible. There are too many easy ways to do things that will absolutely cause you myriads of grief later. I have tried several times to develop my own frameworks, but they always left me feeling like it was too much work

....snipped boldly from: http://www.geistinteractive.com/node/16

--sd

[sullyman]

Hi Soren,

Is this right for my Calc to turn the Field Red if it is a Sat/Sunday

My second field adds 2 days from the first field so would the calc be the following?

Field1 + 172800 + Mod ( Self - 1 ; 7 ) > 4

[comment]

using a calc on Date Fields that will flag weekends (Sat/Sun)

If 2 days are 172800, then your field must be a Timestamp, not Date. So perhaps:

Mod ( GetAsDate ( Self ) - 1 ; 7 ) > 4

[sullyman]

Hi Comment,

But where do i put this code. Do i make a calc field or can i right click on the field for conditional formatting and put it there?

Yes, the field is an SQL timestamp field but the Calc is a date field.

Sorry, i'm lost :

Is there something not simpler in Conditional Formatting where field = Saturday and Sunday (Fill Red)

If 2 days are 172800, then your field must be a Timestamp, not Date. So perhaps:

Mod ( GetAsDate ( Self ) - 1 ; 7 ) > 4

Edited February 15, 2008 by Guest

[comment]

It's supposed to go into Conditional Formatting as a formula. But if your calculation field is set to return a Date result, then the formula Field1 + 172800 doesn't make sense. It's not easy to help you...

[sullyman]

Ok Comment. Thanks for your help.

Would you have a demo file with the previous Let Function perhaps so i can study it perhaps?

[sullyman]

Hi Comment,

It's working. I tried it again for the hell of it and it worked. You are a legend!

Just for reference. The following worked

Mod ( GetAsDate ( Self ) - 1 ; 7 ) > 4

Thanks a mill

[Kiele]

Hi comment,

You never cease to amaze me with your abilities, and knowledge.

You could also try:

Mod ( self - 1 ; 7 ) > 4

But for holidays you will most likely need a list of them.

Can you explain to me what is taking place with the Date here.

TIA

Kia

[comment]

I could try - if I understood what your question is.

[Kiele]

Never mind, I got it.

[Søren Dyhr]

: ....Let's hear your explanation then!

--sd

[Kiele]

sure, what part didn't you understand?

"Never mind" or "I got It"

LOL

Kia

[Søren Dyhr]

I could be mistaken here, but it gives the impression that you accept spoon-fed knowledge without questioning?

Just that it have happened before:

http://fmforums.com/forum/showpost.php?post/245665/

--sd

[Kiele]

I could be mistaken here, but it gives the impression that you accept spoon-fed knowledge without questioning?

Are you implying that I can't think for myself?

I have a suggestion, skip my questions if you can't say anything nice.

Kia

[The Big Bear]

Kiele

It seems like all Soren wanted was for you to explain how you fixed the problem so that other persons who might have the same problen or a similar problen can use your explaination.

[Kiele]

I didn't fix a problem, I had a question on how the Mod Calculation worked in this case. Soren is way smarter than I when it comes to calculations, so any insight that I have would have, wouldn't be for his needs. However, I don't mind sharing how the light was turned on, with you.

I used a Date field with the calendar

and

Three calculations fields, to step the Mod Calculation.

Mod ( DateField ; 7 )

Mod ( DateField -1 ; 7 )

Mod ( DateField -1 ; 7 ) > 4

Then by changing the date, I could see the results for the different parts.

HTH

Kia

[Søren Dyhr]

Alright here it goes, every date correspond to an integer, where the first of january in year 0001 is where it all starts with 1 and where today the 20th of february 2008 is corresponding to 733092.

If we look at Mod( is it the remainder after number is divided by a divisor here 7 ... this means that an arbitrary chosen integer will return another integer in this range {0;1;2;3;4;5;6} ... Seven as divisor is of course the number of days in the week and very much to our luck is saturday and sunday adjacent days so Comment have chosen an offset -1 (the day before) to make the Mod( calc return 5 or 6 in weekend dates.

This means equal results could be obtained by this calc:

Mod ( theDate + 1; 7 ) < 2

But what I have put out a "warrant" for, was the first part, namely how dates are stored in filemaker behind the scenes. It's important to know how the typecasts behave, instead of trial and error in the given situation.

It's bad praxis to just accept the state of things, just on simple and randomly chosen studies of thier behaviours. One could by accident pledge allegiance to the emperors new clothes that way!

So I'm not accusing you for not being able to think for your self, but instead incline on consensus on what just might be a hearsay:

http://www.sciencedaily.com/releases/2008/02/080214114517.htm

--sd