Formula to account for time when time is moved back to previous day

[Livin404]

Greetings, I need a little help completing a formula to take in account of subtracting 30 minutes of time if it were to occur somewhere between 0000 and 0025. We have a given time where only the 30 minutes prior and after are important to us. Thank you

I have the time 0850 in Column D. In Column H I converted it to time

=TIMEVALUE(LEFT(D2,2)&":"&MID(D2,3,2))

to get 8:50. In Column I subtracted 30 minutes

=(H2-0.5/24) to get 8:20. In Column J

I added 30 minutes

=(H2+0.5/24)

to get 9:20. In column K I combined I and J and converted back

=TEXT(I3,"hh:mm")&"-"&TEXT(J3,"hh:mm")

to text to get 08:20-09:20. Lastly I used Substitute to remove the colon =SUBSTITUTE(K2,":","").

 

[Tetra201]

Try using the following formula in cell I2:

=MOD(H2-0.5/24,1)

 

[Livin404]

Thank you perfect!