time, minus 15 mins

[jamiepullen]

hi, i have

=TIME(LEFT(F17,FIND("h",F17)-1),RIGHT(F17,2),0)

which gives a time in the format 9:30PM, or 10:30AM etc.

How would i change the formula to give me a time minus 15 mins

Eg, if the above formula gave me 8:45PM, i would want it to show 8:30PM

Regards, jamie

[VBA Noob]

Maybe

=TIME(LEFT(F17,FIND("h",F17)-1),RIGHT(F17,2)-15,0)

VBA Noob

[oldchippy]

Hi,

May be something like this?

=TIME(LEFT(F17,FIND("h",F17)-1),RIGHT(F17,2),0)-0.0104166666666667

0.0104166666666667 being 15 mins expressed as a decimal part of a day

[daddylonglegs]

Try

=MOD(SUBSTITUTE(F17,"h",":")-"0:15",1)

format as time

[jamiepullen]

they all work, thanks all!

Regards, jamie