IF statement adding a day to a date from 12:00am - Help!

[MurraySMTH]

Hello,

I am trying to solve a simple IF statement by adding one day to a date from a cell containing time.

=IF(L3>Time(12,00,0)Datevalue(J3+1))

So if L3 is higher that 12:00am add one day to the date in J3

If this is not the case, no change to date.

Thank you for any effort provided! Love this forum.

 

[Joe4]

MurraySMTH,

Sorry, it still isn't exactly clear to me. I think the fact that you have 3 long-time members reply, and none can really agree on what you are after shows that your question needs to be clarified. Remember, while it is clear to you because you have been working on it, all that we have to go on is what you tell us.

I recommend walking us through an actual example every step of the way. For example, pick any row, tell us is what is in each cell, and based on that what should happen and where. Just explain all the logic and details in plain English (no formulas, since those weren't working for you in the first place).

 

[MurraySMTH]

MurraySMTH,

Sorry, it still isn't exactly clear to me. I think the fact that you have 3 long-time members reply, and none can really agree on what you are after shows that your question needs to be clarified. Remember, while it is clear to you because you have been working on it, all that we have to go on is what you tell us.

I recommend walking us through an actual example every step of the way. For example, pick any row, tell us is what is in each cell, and based on that what should happen and where. Just explain all the logic and details in plain English (no formulas, since those weren't working for you in the first place).

Great Suggestion Joe,

Column K and L have time-span start times and time-span end times, I will need to add one day to the date found in column J, only if column L goes on to the next day.

So 10:15pm in column K is the start while column L is 4:45am is the end which goes into the next day. I will then need to add 1 day to column J, if this occurs. If it does not, nothing should change.

 

[Rick Rothstein]

Oh, so that is what you wanted. Okay, give this a try...

=J3+DAY(L3+K3)-DAY(J3+K3)

 

[MurraySMTH]

Rick, When it adds a day to column J, it is giving me the result of 1/0/1900

 

[MurraySMTH]

Rick, Possibly a circular reference problem?

 

[Joe4]

Column K and L have time-span start times and time-span end times, I will need to add one day to the date found in column J, only if column L goes on to the next day.

So 10:15pm in column K is the start while column L is 4:45am is the end which goes into the next day. I will then need to add 1 day to column J, if this occurs. If it does not, nothing should change.

Ok, you still haven't told us everything. Let's look at the first example you highlighted. So the calculated date would be 3/13/2020.
But what are you actually doing with this date? Are you putting it somewhere (like in column M), or are you overwriting some value?

Also, I think we need to know what the date portion of columns K and L actually are.
If you temporarily change their format to show both date and time, what does it show for the date part?

 

[Rick Rothstein]

No, just a crap, couldn't-work-if-it-tried formula. Let me rethink this and get back to you.

 

[Rick Rothstein]

Assuming the difference between start and end times is never more than 24 hours, give this a try...

=J3+(L3<K3)

 

[MurraySMTH]

Ok, you still haven't told us everything. Let's look at the first example you highlighted. So the calculated date would be 3/13/2020.
But what are you actually doing with this date? Are you putting it somewhere (like in column M), or are you overwriting some value?

Also, I think we need to know what the date portion of columns K and L actually are.
If you temporarily change their format to show both date and time, what does it show for the date part?

Joe,

Yes I just want to add one day to column j. so 3/12/2020 would be 3/13/2020...

If i filter date and time 1/0/00 12:00AM

I am adding this formula to column J.

 

[MurraySMTH]

Assuming the difference between start and end times is never more than 24 hours, give this a try...

=J3+(L3<K3)

still getting 1/0/1900