Conditional formatting based on date

[JD2775]

Hi guys,

I currently have a grid in a layout (8x4) designed with portals. I have each cell conditionally formatted so when I tap on the cell it autopopulates todays date and the cell turns red. This is done via a script (to populate the date) and conditional formatting (for the color) What I want, and I am not sure its possible, is for the cell to turn a differnet color depending on the day of the week. 7 days a week. 7 colors. Is that possible? If not, can it be formatted based off the day number? i.e, 01, 08, 15, 22, 29 would be yellow, 02, 09, 16, 23, 30 would be green......

**EDIT**- I would prefer actually if it could be based off the day number (option 2) as opposed to the Day of the week Name.

My current calculation for the conditional formatting is:

not IsEmpty ( Seed Germination_CELL__FlatID Match::GermDate )

If that happens, the cell currently turns red when tapped. (this is on an iPad btw)

Any ideas?

Thanks

[doughemi]

You need to set up conditions for each day:

not IsEmpty ( Seed Germination_CELL__FlatID Match::GermDate ) and (DayOfWeek(Seed Germination_CELL__FlatID Match::GermDate ) = 1) //set fill to red

then add

not IsEmpty ( Seed Germination_CELL__FlatID Match::GermDate ) and (DayOfWeek(Seed Germination_CELL__FlatID Match::GermDate ) = 2) //set fill to orange

etc.

[JD2775]

Thanks Doug. I take it the DayofWeek is Mon-Sun? That will work, I think the other option is too complicated

Thanks again

[comment]

not IsEmpty ( Seed Germination_CELL__FlatID Match::GermDate ) and (DayOfWeek(Seed Germination_CELL__FlatID Match::GermDate ) = 1)

The first test is redundant: If DayOfWeek ( date ) = 1, then date cannot be empty.

can it be formatted based off the day number? i.e, 01, 08, 15, 22, 29 would be yellow, 02, 09, 16, 23, 30 would be green......

Try:

Mod ( Day ( date ) ; 7 ) = 1

etc. Note that Mod ( n ; 7 ) runs from 0 to 6 - so you do need to check for not IsEmpty ( date) when the Mod() is supposed to return 0.

[doughemi]

I take it the DayofWeek is Mon-Sun?

Not quite.

From Filemaker Help:

Format

DayOfWeek(date)

Parameters

date - any calendar date

Data type returned

number

Description

The number 1 represents Sunday, 2 represents Monday, 3 represents Tuesday, and so on.

So (thanks to Comment for pointing out the redundancy) the correct series of calculations would be:

DayOfWeek(Seed Germination_CELL__FlatID Match::GermDate ) = 1 //set desired fill color for Sunday

DayOfWeek(Seed Germination_CELL__FlatID Match::GermDate ) = 2 //set desired fill color for Monday

repeated for days 1-7, with each line causing a different fill color. This will not care what day of the month the GermDate is.