Here's a solution for you based on the problem as originally defined:
Cell A1 contains a sample date/time in your format.
B1 translates that to an Excel date/time with this formula:
=DATE(LEFT(A1,2),1,1)+MID(A1,3,3)-1+MID($A1,7,2)/24+RIGHT($A1,2)/1440
This uses text functions to parse the pieces and builds the date from the year, using January 1, then adds the day count (and subtracts 1 since the year can't start on January 0). Since Excel stores times as fraction of a day, the hour value is divided by 24 and the minute value is divided by 1440 minutes in a day.
Now your entry is in a form on which Excel can do date/time math. This is the cleanest approach since you could potentially run into values where you would otherwise need to adjust the day count or year, and doing power-of-ten math on times gets messy.
C1 contains the date/time after subtracting 3 hours 45 minutes:
=B1-TIME(3,45,0)
D1 translates the result back to your format:
=TEXT(C1,"yy")&TEXT(INT(C1)-DATE(YEAR(C1),1,0),"00#")&"/"&TEXT(MOD(C1,1),"hhmm")
It concatenates the pieces using &
to build the string.
- Three digit day is the date of the result minus "January 0" of the year of the result at the same time of day (the time is part of the stored value and rounding could affect the day count). The INT function extracts the date of the result at midnight to match the time of the calculated January 0 date.
- The MOD function provides the fractional part of the result, which is the time portion, and uses the TEXT function to build the hours and minutes display in 24 hour format.
You could do this all in one, difficult to maintain, cell by substituting formulas for cell references. In D1, replace each C1 reference with the formula in C1 after the initial =
. Then replace each B1 reference with the formula in B1 after the initial =
.
Your question describes an IF condition, but you don't provide enough information for it. Substitute F14 instead of A1 in your formula to refer to the correct entry cell. The IF in cell K14 would look like this:
=IF(D14="C17",formula based on above answer,"")
Don't duplicate the initial =
from my answer formula when you insert the formula from the answer into the IF. I assumed a null (the empty quotes, which will give you a blank cell), for the false condition, but you can make that anything you want.