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

 

[James006]

Hi,

For your military time to be converted to standard Excel time, you can use following formula

=TIMEVALUE(LEFT(A1,2)&”:”&RIGHT(A1,2)).

Once you have this result, you can perform the calculations you require ...

 

[awoohaw]

will this work?:

mr excel questions 22.xlsm
	A	B
4	time	calculation
5	2023-04-04 20:16	2023-04-05 00:00
6	2023-04-04 08:16	2023-04-04 00:00
RattlingCarp3048

Cell Formulas
Range	Formula
B5:B6	B5	=INT(A5)+IF(MOD(A5,1)>=TIME(13,0,0),1,0)

 

[RattlingCarp3048]

Hi,

For your military time to be converted to standard Excel time, you can use following formula

=TIMEVALUE(LEFT(A1,2)&”:”&RIGHT(A1,2)).

Once you have this result, you can perform the calculations you require ...

this didnt work. i got a #NAME error.

 

[awoohaw]

@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.

 

[Fluff]

Do you want before 1300 or before 3PM, they are not the same

 

[RattlingCarp3048]

will this work?:

mr excel questions 22.xlsm
	A	B
4	time	calculation
5	2023-04-04 20:16	2023-04-05 00:00
6	2023-04-04 08:16	2023-04-04 00:00
RattlingCarp3048

Cell Formulas
Range	Formula
B5:B6	B5	=INT(A5)+IF(MOD(A5,1)>=TIME(13,0,0),1,0)

this didnt work either. still giving all line items +1 to the date regardless of the time

3/7/23 1:04 PM​

3/7/2023​

3/8/2023​

 

[RattlingCarp3048]

Do you want before 1300 or before 3PM, they are not the same

oh goodness i just realized that too

@awoohaw solution did work. its suppoed to be 3pm (1500). i updated @awoohaw's formula and it worked like a charm.

 

[awoohaw]

this didnt work either. still giving all line items +1 to the date regardless of the time

3/7/23 1:04 PM​

3/7/2023​

3/8/2023​

ignore.

 

[awoohaw]

Happy to help.

Best wishes!