If statement with date

[Jacktheripperz]

Hi I have a text box that contains the following if statement

=if (DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE])), 'DD/MM/YYYY'), '', 'ALERT *Missing Fridays Data for jobs')  

which was supposed to warn for missing data on a friday. but this only works the following day because the max wont be that day so i tried to do it with a count

=IF(Count({<JOB_DATE = {"$(=DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1))"}>} JOB_DATE) > 0, '', 'ALERT *Missing Fridays Data for jobs')

 this didnt work because JOB_DATE contains date and time.
but floor doesnt work in the count so this doesnt work at all 😞

=IF(Count({<Date(Floor(JOB_DATE), 'DD/MM/YYYY') = {"$(=DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1))"}>} JOB_DATE) > 0, '', 'ALERT *Missing Fridays Data for jobs')

does anyone have an idea how to fix this? seems simple but i have run out of solutions 

[Or]

Max() takes a second, optional parameter for rank. You could use Max(Field,2) to get the second highest value.

I'm also kind of confused about all your assorted date functions which seem cumbersome and unnecessary, but that's not really related to the problem, I guess.

DayName() will return the date without the time aspect (as will Floor()), but the former will treat the field as a date and the latter as a number)

[Jacktheripperz]

Hi thanks for the reply.

the multiple functions were attempts to get the date I suppose I could make it really cumbersome and have 7

nested If statements along the lines of

=IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE])), 'DD/MM/YYYY'), '', 
IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE]), 2), 'DD/MM/YYYY'), '', 
IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE]), 3), 'DD/MM/YYYY'), '', 
IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE]), 4), 'DD/MM/YYYY'), '', 
IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE]), 5), 'DD/MM/YYYY'), '', 
IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE]), 6), 'DD/MM/YYYY'), '', 
IF(DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(MAX(FLOOR([JOB_DATE]), 7), 'DD/MM/YYYY'), '', 'ALERT *Missing Fridays Data for jobs')))))))

this seems over complex but would work  guess. 
i just think there must be a simple way to compare a date to a date with time column and display a message in there.
=if (DATE(TODAY() - MOD(WEEKDAY(TODAY()) + 2, 7) - 1, 'DD/MM/YYYY') = DATE(FLOOR([JOB_DATE])), 'DD/MM/YYYY'), '', 'ALERT *Missing Fridays Data for jobs')

im guessing this isnt working because it is comparing todays date with a list of dates(array)?

 I know that I can see 28/03/2025 11:02:22 in the JOB_DATE but if i use this it shows it as a string.
If( Len(Num(JOB_DATE))=5 , 'Date', 'String')
but it behaves as a date (and time)

hence im a little lost how to get it to behave 🙂

[Or]

Your original approach should work (though the Date() wrappers for example are not necessary) if you just fix the Max(), I think?

[Jacktheripperz]

To answer my own question in case anyone else wants to have a warning about missing data 🙂

this works nicely to warn me of missing data 

IF(Index(Trim(Concat(DISTINCT Date(Floor([JOB_DATE]), 'DD/MM/YYYY'), ', ')), Date(Today() - Mod(WeekDay(Today()) + 2, 7) - 1, 'DD/MM/YYYY')) > 0, '', 'ALERT *Missing Fridays Data for jobs')