0

I want to do the following in cell K14: If cell D14 contains C17, take cell F14 and subtract 3 hours and 45 minutes.

The date/time format I am using is a Julian Date/military time, so 16271/1845 minus the 3 hrs 45 minutes would be 16271/1500. These date/time values are stored as a text string. The Julian date is the two digit year followed by the three digit day of the year.

15
  • 1) What happens if D14 contains something else? 2) is C17 a literal text value or a cell reference? 3) Are the date/time values stored in that format, or stored as Excel date/time values and formatted to display like that?
    – fixer1234
    Sep 27, 2016 at 23:34
  • Well D14 could contain 3 things 1. C17 2. KC135 3. B7 It will be literal text values. And the format is not stored anywhere, is there a way for me to make my own format within excel? If not I could change the format to make it easier
    – Deaula
    Sep 28, 2016 at 0:17
  • Just found a way to make my own format, however the Julian date is not a code in excel so that is an issue.
    – Deaula
    Sep 28, 2016 at 1:00
  • Maybe there is a way to just keep the first half of the numbers (e.x. 6271) and just subtract the military time format from the second half?
    – Deaula
    Sep 28, 2016 at 1:04
  • 1
    If you have a time before 3:45AM on January 1, the year will also be affected. You've managed to pick the worst case format for both date and time in terms of complicating the solution. You might want to just convert what you have to Excel date/time, do the math, and then build the displayed value if you want it redisplayed like that.
    – fixer1234
    Sep 28, 2016 at 1:56

1 Answer 1

0

Here's a solution for you based on the problem as originally defined:

screenshot

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.

1
  • @creidhne, great catch! Thanks. I corrected the formula and incorporated some of your simpler code.
    – fixer1234
    Sep 28, 2016 at 21:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .