Formula to add 1 day to date

Laurie Mason

I'm having trouble with a formula to add one day to a date based on a condition. I'm sure it is something terribly simple that I'm just missing right now.

My formula worked previously when I had AND in the formula with two conditions, but when I only have one condition it adds a 1 to the end of the date rather than adding 1 day.

Yes, I have confirmed that the Date Entered, and Date (Adjusted for Night Shift) are both Date columns.

Here is the old formula that worked. But I do not need or want the hour = 6 to be part of the formula anymore.

=IF(AND(RIGHT(Created@row, 2) = "PM", RIGHT(Created@row, 7) = 6), [Date Entered]@row +1, [Date Entered}@row)

ECarlson_PCG

Hi Laurie,

The only way I could reproduce what you're seeing is by making Date Entered a Text/Number column type instead of Date. I know you mentioned its a Date column already.
What is the formula in Date Entered?

Best,

Emily

Laurie Mason

Thank you @ECarlson_PCG.

This issue seems to be caused by the formula in the Date Entered column. While the column is a Date column regardless, when I use the =LEFT(Created@row, 8) it doesn't seem to recognize it as a date. I previously used the =DATEONLY(Created@row) formula, but that would sometimes through things off due to the system UTC time.

Paul Newcome

Yes. The LEFT function outputs text values. Try this instead of the LEFT:

=DATE(VALUE("20" + MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2)))

Laurie Mason

Thank you @ECarlson_PCG.

This issue seems to be caused by the formula in the Date Entered column. While the column is a Date column regardless, when I use the =LEFT(Created@row, 8) it doesn't seem to recognize it as a date. It must see the date as text in this case.

I previously used the =DATEONLY(Created@row) formula, but found the need to adjust this because it would sometimes through things off due to the system UTC time. So instead of adding one day it would add two.

However, I'm still perplexed as to why the =IF(AND(RIGHT(Created@row, 2) = "PM", RIGHT(Created@row, 7) = 6), [Date Entered]@row +1, [Date Entered}@row) formula works even with the =LEFT(Created@row, 8) formula in the Date Entered Column.

Paul Newcome

@Laurie Mason That is correct. The LEFT function outputs a text string. To get recognized as a date, try the method in my last post.

The reason the other formula appeared to work sometimes is because the AND condition was not being fully met and was just replicating your text string with the "value if false" part of the IF.

TVang

@Laurie Mason, give the following a try.

=DATEONLY(IF(FIND("PM", Created@row) = 0, 0, 1) + Created@row) + " " + Shift@row

The expression IF(FIND("PM", Created@row) = 0, 0, 1) returns 0 or 1. Add that to the timestamp and then return DATEONLY().

DATEONLY() + " " + Shift@row returns the string you need in [Index Helper].