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

#### Livin404

##### Active Member
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
Excel Formula:
``=TIMEVALUE(LEFT(D2,2)&":"&MID(D2,3,2))``
to get 8:50. In Column I subtracted 30 minutes
Excel Formula:
``=(H2-0.5/24) to get 8:20. In Column J``
Excel Formula:
``=(H2+0.5/24)``
to get 9:20. In column K I combined I and J and converted back
Excel Formula:
``=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,":","").

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Tetra201

##### MrExcel MVP
Try using the following formula in cell I2:

=MOD(H2-0.5/24,1)

#### Livin404

##### Active Member
Thank you perfect!

Replies
14
Views
390
Replies
5
Views
199
Replies
5
Views
91
Replies
17
Views
737
Replies
6
Views
397

1,127,242
Messages
5,623,573
Members
415,981
Latest member
Baltwin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back