if formula with date/time field

[RattlingCarp3048]

There is probably an easy solution i am overlooking but if someone could get me in the right direction that would be great.

I have a data set that is extracted from a program. The date and time is in one field formatted to military time. I am trying to write a formula that states if the time is <= 1300, then put original date+1, if not put the original date. Excel no matter what combination or format i write the formula it sill +1 to everything regardless of the time.

current formula =IF(I2<="3 PM",I2+1,I2)
previously tired =IF(I2<="1300",I2+1,I2)

Original: 4/4/23 16:16​	what it should be: 4/5/2023​	what formula returned: 4/5/2023​	this was after 3pm on 4/4 so formula should return next day's date of 4/5
4/5/23 5:56​	4/5/2023​	4/6/2023​	this is before 3pm on 4/5 so formula should return original date of 4/5

 

[RattlingCarp3048]

@RattlingCarp3048 , I think you have your signs in the wrong order. In your paragraph you use < 1300 to add one to the day. But, in your table of expectations you seem to be wanting to add if the hour is beyond 1300.

Please look at the solution offered in post #3.

your formula is correct. i was mixing up 1300 and 1500 military time. its supposed to be 1500 (3pm) not 1300 (1pm). i updated your formula and it works. i marked it as the solution. sorry for the confusion on my part

 

[James006]

Your initial format is not military ...

Awoohaw formula works fine ... just add time

=INT(E2)+IF(MOD(E2,1)>=TIME(13,0,0),1,0)+MOD(E2,1)

 

[awoohaw]

Your initial format i not military ...

Awoohaw formula works fine ... just add time

=INT(E2)+IF(MOD(E2,1)>=TIME(13,0,0),1,0)+MOD(E2,1)

@James006 , i don't think he was asking for a new time, just the date.

 

[James006]

@James006 , i don't think he was asking for a new time, just the date.

Guess missed the point right from the very first message

 

[RattlingCarp3048]

yes, just the date. awoohaw's formula did give a date/time format so i just reformatted the cells to hide the time since it isnt necessary. its working great now. thanks everyone.

 

[awoohaw]

yes, just the date. awoohaw's formula did give a date/time format so i just reformatted the cells to hide the time since it isnt necessary. its working great now. thanks everyone.

yeah, when I'm working with date/time questions I always try to format with both components. And seldom remember to reformat.
best wishes!