DAY Function

Carol-Anne Cerbone

Hi everyone -

I have 2 helper columns (Column Names: Apps scheduled for today and Apps scheduled yesterday) that is supposed to look at the create date and today's date to determine if a checkbox should be checked or not.

Here are the formulas:

Apps scheduled for today: =IF(AND(YEAR(Created@row) = YEAR(TODAY()), DAY(Created@row) = DAY(TODAY())), 1)

Apps scheduled yesterday: =IF(AND(YEAR(Created@row) = YEAR(TODAY()), DAY(Created@row) = (DAY(TODAY())) - 1), 1)

In looking at the Create Date the date is 11/07/2023 8:48 PM. The checkbox for "Apps scheduled for today" is still checked even though today is 11/08/2023. Is it because of the time of day?

Eric Law

@Carol-Anne Cerbone Yes, it is because created is time day. All you'll need to do is add a DATEONLY around the Created@row, for example.

=IF(AND(YEAR(DATEONLY(Created@row)) = YEAR(TODAY()), DAY(DATEONLY(Created@row)) = DAY(TODAY())), 1)

Hope that works

Eric Law

@Carol-Anne Cerbone Yes, it is because created is time day. All you'll need to do is add a DATEONLY around the Created@row, for example.

=IF(AND(YEAR(DATEONLY(Created@row)) = YEAR(TODAY()), DAY(DATEONLY(Created@row)) = DAY(TODAY())), 1)

Hope that works

Carol-Anne Cerbone

@Eric Law - I tried your suggestion and unfortunately it didn't work. I still have boxes checked that shouldn't be checked. I copied / pasted the formula you provided - =IF(AND(YEAR(DATEONLY(Created@row)) = YEAR(TODAY()), DAY(DATEONLY(Created@row)) = DAY(TODAY())), 1). If you have any other suggestions, I would greatly appreciate.

Eric Law

@Carol-Anne Cerbone can you share a screenshot?

Try this formula instead. You syntax for the today function was off, and I streamlined it so you don't need the AND

for today, =IF(DATEONLY(Created@row) = TODAY(), 1)

for yesterday, =IF(DATEONLY(Created@row) = TODAY(-1), 1)

Carol-Anne Cerbone

@Eric Law - that didn't work either. but what I find interesting is I created a column to test just DATEONLY function. When I did that, it put a date of 11/9/2023 but the created date is 11/8/2023. See screenshot below. The end user that entered in this information is located in CA so I'm sure that has something to do with it. Any other thoughts?

Eric Law

@Carol-Anne Cerbone I tested the formula I posted to you and it worked. Are you placing it in a checkbox column?

Carol-Anne Cerbone

@Eric Law - I am. Below are screenshots for both. You will notices that the Apps Added today is still checked even though the Created Date was yesterday. You will also notice in the first screenshot that some of the apps from yesterday did check the correct box but not all of them.

Apps Added Yesterday

Apps Added today:

Eric Law

@Carol-Anne Cerbone That is interesting... I have no clue on what is the issue then. You MAY have better luck with creating a DATEONLY Created column and then comparing that to the TODAY function. This issue may be due to time zones and what nots. Sorry 😑

Carol-Anne Cerbone

@Eric Law - I appreciate the help! I agree, I'm thinking it has to do with the time zones. i'm going to monitor it throughout the day and see if the checkbox moves later on.