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]

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.

One way:
=IF((L3-INT(L3))>0,INT(J3)+1,INT(J3))

 

[gaz_chops]

Try

=IF(L3>0.5,J3+1,J3)

 

[Rick Rothstein]

If cell J3 contains a real date (not a text one), then this should work...

=INT(J3)+(L3>0.5)

 

[Joe4]

Rick and gaz_chops,

Isn't 12:00 AM midnight and not noon (12:00 PM is noon, which is half a day)?
Since their formula wasn't working correctly, I went by their description and not their formula.

 

[MurraySMTH]

These are the formats i am using.

 

[gaz_chops]

Oops i misread it! Thinking of 12 hours!!

 

[Rick Rothstein]

Rick and gaz_chops,

Isn't 12:00 AM midnight and not noon (12:00 PM is noon, which is half a day)?
Since their formula wasn't working correctly, I went by their description and not their formula.

The only reason I assumed the OP meant noon is because all times are greater than midnight (except midnight itself I guess), so you would always be adding 1. Since that would make the question's wording strange (why wouldn't the OP have just asked how to add one day), I assume the OP meant midday.

 

[Joe4]

MurraySMTH,

What column are you putting this formula in?

Even though K and L only show times doesn't necessarily mean they don't have a date component (actually, all valid time entries do, it is just that the date part is hidden by using a format that only shows time). So how are K and L being populated? Manually? By a formula (if so, please post the formulas)?

 

[MurraySMTH]

Joe,

I will be adding 1 day to column J that will determine the time from column L