Subtract 15minutes from start time in a time range

[JMS727]

Hi,

I have an excel column that is in general form with time ranges (IE. 8:30AM-12:30PM, 9:15AM-11:15AM)
I need a separate column that has this time range with a 15minute sooner start time but same end time (IE. 8:30AM-12:30PM is reported as 8:15AM-12:30PM).
Is there a formula that can pull this in without me adding extra columns or forcing manual adjustments? I can trim out 8:30 but seem to get stuck subtracting 15minutes and adding back the end time. I have almost 200 time range combinations to adjust...

Any help would be appreciated. thank you!

[Tony Valko]

Is there a formula that can pull this in without me adding extra columns or forcing manual adjustments?

No, if you want to use a formula then it must be entered in another cell.

If your time ranges were in 2 cells like this:

A1 = start time 8:30 AM (as a true Excel time value)
B1 = end time 12:30 PM (as a true Excel time value)

Then you could do it without a formula.

[JMS727]

Unfortunately, the data for the time range has to be in the same column for both instances because it is imported into a separate web displaying database. I am trying to avoid extra formating in columns that require being hidden.

[Tony Valko]

Maybe someone (not me!) can do this with a macro?

If you don't get a useable solution after a reasonable amount of time (whatever that is?) try starting a new thread in the VBA programming forum:

http://www.excelforum.com/excel-programming-vba-macros/

[GeneralDisarray]

--edit--

does the attached file work? this is the formula, see the uploaded example for the context :D

=TEXT((LEFT(C3,FIND("M",C3,1)-2)-15/(24*60)),"HH:MM") & RIGHT(C3,LEN(C3)-FIND("M",C3,1)+2)

[JMS727]

Oh you are awesome thankyou!! It is superduper close! This is the result....the ones that fall on the hour are funky. Suggestions?
1:45PM-4:45PM 01:30PM-4:45PM
1:00PM-4:00PM 00:45PM-4:00PM
1:45PM-4:45PM 01:30PM-4:45PM
1:00PM-4:00PM 00:45PM-4:00PM
1:45PM-4:45PM 01:30PM-4:45PM
8:30AM-11:30AM 08:15AM-11:30AM
9:15AM-12:15PM 09:00AM-12:15PM
8:30AM-11:30AM 08:15AM-11:30AM
9:30AM-12:30PM 09:15AM-12:30PM
1:00PM-4:00PM 00:45PM-4:00PM
1:00PM-4:00PM 00:45PM-4:00PM

[GeneralDisarray]

darn, it's the PM thing. will get back to you

[GeneralDisarray]

ok, got it the formula becomes this:

=REPLACE(TEXT(IFERROR(LEFT(C3,FIND("M",C3,1)-2)+CHOOSE((AND(FIND("-",C3,1)<FIND("PM",C3,1),LEFT(C3,2)<>"12"))*1 + 1,-15/(24*60),0.5-15/(24*60)),LEFT(C3,FIND("M",C3,1)-2)-15/(24*60)),"hh:mm AM/PM") & RIGHT(C3,LEN(C3)-FIND("-",C3,1)+1),6,1,"")


dang. got kinda long :D and you probably don't need it anymore, but for the fun of doing it ... it be done

[JMS727]

So close again!! I really appreciate your help!
Apparently, it is still flipping the AM versus PM....even in your example it takes 1:45PM and makes it 1:30AM.
I manually changed all of my excel, but if you can figure out how to get the correct AM/PM switch, I will need to do this again in August (its a twice/year deal).

[Tony Valko]

Hmmm...

I thought you didn't want to use a formula in another cell?

[GeneralDisarray]

ok, this is pretty ugly. Noon and Midnight were and pain in the butt, as was the 12 hour clock. Using a 24 hour clock would be much easier.. but i think this is it.

=REPLACE(TEXT(IFERROR(LEFT(C18,FIND("M",C18,1)-2)+CHOOSE((LEFT(C18,7)="12:00AM")*2+(LEFT(C18,7)="12:00PM")*2+(FIND("-",C18,1)<FIND("PM",C18,1))*1 + 1,0.5-15/(24*60),-15/(24*60),-15/(24*60),0.5-15/(24*60)),LEFT(C18,FIND("M",C18,1)-2)-15/(24*60)),"hh:mm AM/PM") & RIGHT(C18,LEN(C18)-FIND("-",C18,1)+1),6,1,"")