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

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
I added 30 minutes
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,":","").



ETA formula.JPG
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,724
Members
415,923
Latest member
Kam80

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top