Obtaining a decimal total hours from military time entries (my solution works only in Google Sheets)

gluonman · #6 03-21-2017, 01:56 PM

Alright, PecoFlyer's solution came the closest to what I needed. I only needed to add in the additional logic I talked about in my original post in order to adapt it, and it's now working perfectly. That and it's a significantly more efficient solution than that beast I wrote. Thank you so much.

xor and trevorc's solutions also work, in case you need to report totals as a time value, but their solutions do not provide decimal values for totals, which I had specified in my post. But still, thanks for contributing and being helpful.

In case you want to see my final solution, a relatively simple adaptation on PecoFlyer's formula, here it is:

Code:

=IF(
   OR(
      AND(
         ISBLANK(F9),
         ISBLANK(G9),
         ISBLANK(I9),
         ISBLANK(J9)
      ),
      AND(
         ISBLANK(F9),
         ISBLANK(I9)
      ),
      AND(
         ISBLANK(G9),
         ISBLANK(J9)
      ),
      AND(
         ISBLANK(G9),
         ISBLANK(I9)
      ),
      AND(
         NOT(ISBLANK(F9)),
         NOT(ISBLANK(G9)),
         XOR(
            NOT(ISBLANK(I9)),
            NOT(ISBLANK(J9))
         )
      ),
      ISBLANK(F9),
      ISBLANK(G9)
   ),
   "",
   IF(
      AND(
         ISBLANK(I9),
         ISBLANK(J9)
      ),
      (TIMEVALUE(TEXT(LEFT(G9,LEN(G9)-2),"00")&":"&RIGHT(G9,2))-TIMEVALUE(TEXT(LEFT(F9,LEN(F9)-2),"00")&":"&RIGHT(F9,2)))*24,
      (TIMEVALUE(TEXT(LEFT(G9,LEN(G9)-2),"00")&":"&RIGHT(G9,2))-TIMEVALUE(TEXT(LEFT(F9,LEN(F9)-2),"00")&":"&RIGHT(F9,2))+TIMEVALUE(TEXT(LEFT(J9,LEN(J9)-2),"00")&":"&RIGHT(J9,2))-TIMEVALUE(TEXT(LEFT(I9,LEN(I9)-2),"00")&":"&RIGHT(I9,2)))*24
   )
)

Thanks again.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
subtract date and time and convert to decimal hours ?	DBenz	Excel	5	01-26-2017 05:38 AM
Formula for decimal hours from two date time entries	DBenz	Excel Programming	0	01-15-2015 05:52 AM
Help! Need VBA solution to create CSV file and export to Google Calendar	mister_audioman	Excel Programming	0	01-12-2012 01:07 PM
Converting decimal to hours	jhwtsang	Excel	4	05-16-2011 01:36 PM
Convert hours in decimal	ghostones	Excel	1	12-29-2009 09:17 PM