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

gluonman · #1 03-21-2017, 10:17 AM

Hello,

I'm an Excel noob and I'm struggling with a time-reporting problem. Before I start explaining the problem, let me assure you I've spent a lot of time trying to find a solution to this problem via Google searching and forums like stackoverflow, etc. I did work out a solution which I tested in Google Sheets (assuming that any solution working there would likely also work in Excel 2016), and I tested it in LibreOffice as well (I do my work in a Gentoo Linux environment, so I can only use the Microsoft Excel 2016 at my company's office to test what I developed on my own Linux machine). Based on my tests, the solution works well, but it fails when I open the same spreadsheet in Excel 2016, at least on the Windows 7 computers at work, (which is where it actually needs to work, more importantly than in Google Sheets, for reasons I'll make clear in the next paragraph). I'll show my solution below, but first let me explain my actual problem in detail:

I'm working on a spreadsheet that will be used for timesheets for a small company. The employees enter their start and end times for two separate periods in military time, and I need a separate cell to collect the entered times and calculate a decimal total of hours worked. Employees tend to physically enter 700 to create 07:00 and 1600 to create 16:00, etc. Their data entry behaviour is not expected to change, so the cells are formatted such that their 700 entry appears as 07:00, etc. (i.e. 00":"00). To ensure I'm being completely clear, it looks like:

Start (1st Period): ____ End (1st Period): ____ Start (2nd Period): ____ End (2nd Period): ____ Total Hours: ____

In this case, the total hours should calculate the total from only the start and end times for the 1st period (for employees who only work one period on any given day and leave the 2nd period entries blank), or calculate the total hours from all four entered times where all four times have been entered. So, to express the logic in pseudocode, we have:

Code:

if [ only first period time slots entered ] {
   total = time duration represented by only the first two time slots
} else if [ all time slots entered ] {
   total = time duration represented by all four time slots
}

In all other cases, no total should be expressed, and so I need to have a number display only under the cases where a total is required (as in the above pseudocode), but "" if not (as opposed to #VALUE or #NULL or some other ugly value).

So the solution I came up with (I've unminified it for you) for this problem is as follows (where F9 = 1st start time, G9 = 1st end time, I9 = 2nd start time, and J9 = 2nd end time):

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(
         ISBLANK(F9),
         ISBLANK(G9)
      ),
      AND(
         NOT(
            ISBLANK(F9)
         ),
         NOT(
            ISBLANK(G9)
         ),
         AND(
            OR(
               NOT(
                  ISBLANK(I9)
               ),
               NOT(
                  ISBLANK(J9)
               )
            ),
            NOT(
               AND(
                  NOT(
                     ISBLANK(I9)
                  ),
                  NOT(
                     ISBLANK(J9)
                  )
               )
            )
         )
      ),
      ISBLANK(F9),
      ISBLANK(G9)
   ),
   "",
   IF(
      AND(
         ISBLANK(I9),
         ISBLANK(J9)
      ),
      INT(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2))))*24+HOUR(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2))))+ROUND(MINUTE(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2))))/60,2),
      INT(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))/60,2)
   )
)

It detects which of the military time cells have been entered and which ones haven't, and displays "" or a calculated total depending on those conditions, following the logic in the pseudo code I wrote in the last full paragraph. This solution gives accurate results in Google Sheets and in LibreOffice, but while it doesn't give errors in Excel 2016, it gives inaccurate numbers. For instance, in Google Sheets, the above code will give us:

700 800 1600 1630 Total: 1.5 <-- correct

But in Excel 2016 we get:

700 800 1600 1630 Total: 10.5 <-- incorrect

And fixing it is not as simple as adding a constant adjustment (such as -9), since it won't apply to every case. I realized eventually that Excel 2016 probably calculates the total from the entered amount (700) rather than the formatted number (07:00), where Google Sheets relies on the formatted number rather than the entered number (honestly just a guess, I could be completely wrong), or else the initial 0 in 0700 is treated differently between the two platforms. Or, maybe, it has something to do with functions like INT(), MINUTE(), or ROUND() giving slightly different results between the two platforms (I assumed they would be identical). So, in an attempt to correct the problem, I tried to add further logic into my formula to detect whether a cell's entered value begins with a 1 or not (e.g. 700 = false and 1600 = true) using the MID() function, in case my issue was a problem with how each platform treats a leading 0. In fact, I found out that some nighttime employees will have end times that occur in the morning, which broke my original code even in Google Sheets given the leading 0 or 3-digit entered number rather than 4, so there does need to be logic to determine first if a time begins with a 1 or not anyway. In any case, based on my additional logical structures, the total now determines whether to try to calculate the value of one cell with LEFT(G9,1)&":"&RIGHT(G9,2) or LEFT(G9,2)&":"&RIGHT(G9,2), for an example. And so in this way I came up with the following solution, which is excessive in its size and quite ugly to behold and difficult to read (there must be a more efficient way). I apologize that it is so large I didn't have the time to unminify it for you (I'm not aware of any online tools to unminify Excel code), but 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(ISBLANK(F9),ISBLANK(G9)),AND(NOT(ISBLANK(F9)),NOT(ISBLANK(G9)),AND(OR(NOT(ISBLANK(I9)),NOT(ISBLANK(J9))),NOT(AND(NOT(ISBLANK(I9)),NOT(ISBLANK(J9)))))),ISBLANK(F9),ISBLANK(G9)),"",IF(AND(ISBLANK(I9),ISBLANK(J9)),IF(AND(MID(F9,1,1)=1,MID(G9,1,1)=1),INT(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2))))*24+HOUR(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2))))+ROUND(MINUTE(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2))))/60,2),IF(AND(MID(F9,1,1)=1,NOT(MID(G9,1,1)=1)),INT(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2))))*24+HOUR(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2))))+ROUND(MINUTE(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2))))/60,2),IF(AND(NOT(MID(F9,1,1)=1),MID(G9,1,1)=1),INT(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2))))*24+HOUR(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2))))+ROUND(MINUTE(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2))))/60,2),IF(AND(NOT(MID(F9,1,1)=1),NOT(MID(G9,1,1)=1)),INT(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2))))*24+HOUR(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2))))+ROUND(MINUTE(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2))))/60,2),"")))),IF(AND(MID(F9,1,1)=1,MID(G9,1,1)=1,MID(I9,1,1)=1,MID(J9,1,1)=1),INT(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))/60,2),IF(AND(MID(F9,1,1)=1,MID(G9,1,1)=1,MID(I9,1,1)=1,NOT(MID(J9,1,1)=1)),INT(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))/60,2),IF(AND(MID(F9,1,1)=1,MID(G9,1,1)=1,NOT(MID(I9,1,1)=1),MID(J9,1,1)=1),INT(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))/60,2),IF(AND(MID(F9,1,1)=1,NOT(MID(G9,1,1)=1),MID(I9,1,1)=1,MID(J9,1,1)=1),INT(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))/60,2),IF(AND(NOT(MID(F9,1,1)=1),MID(G9,1,1)=1,MID(I9,1,1)=1,MID(J9,1,1)=1),INT(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))/60,2),IF(AND(MID(F9,1,1)=1,MID(G9,1,1)=1,NOT(MID(I9,1,1)=1),NOT(MID(J9,1,1)=1)),INT(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))/60,2),IF(AND(MID(F9,1,1)=1,NOT(MID(G9,1,1)=1),MID(I9,1,1)=1,NOT(MID(J9,1,1)=1)),INT(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))/60,2),IF(AND(NOT(MID(F9,1,1)=1),MID(G9,1,1)=1,MID(I9,1,1)=1,NOT(MID(J9,1,1)=1)),INT(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))/60,2),IF(AND(MID(F9,1,1)=1,NOT(MID(G9,1,1)=1),NOT(MID(I9,1,1)=1),MID(J9,1,1)=1),INT(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))/60,2),IF(AND(NOT(MID(F9,1,1)=1),MID(G9,1,1)=1,NOT(MID(I9,1,1)=1),MID(J9,1,1)=1),INT(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))/60,2),IF(AND(NOT(MID(F9,1,1)=1),NOT(MID(G9,1,1)=1),MID(I9,1,1)=1,MID(J9,1,1)=1),INT(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))/60,2),IF(AND(NOT(MID(F9,1,1)=1),NOT(MID(G9,1,1)=1),NOT(MID(I9,1,1)=1),MID(J9,1,1)=1),INT(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,2)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))/60,2),IF(AND(NOT(MID(F9,1,1)=1),NOT(MID(G9,1,1)=1),MID(I9,1,1)=1,NOT(MID(J9,1,1)=1)),INT(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,2)&":"&RIGHT(I9,2))))/60,2),IF(AND(NOT(MID(F9,1,1)=1),MID(G9,1,1)=1,NOT(MID(I9,1,1)=1),NOT(MID(J9,1,1)=1)),INT(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,2)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))/60,2),IF(AND(MID(F9,1,1)=1,NOT(MID(G9,1,1)=1),NOT(MID(I9,1,1)=1),NOT(MID(J9,1,1)=1)),INT(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,2)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))/60,2),IF(AND(NOT(MID(F9,1,1)=1),NOT(MID(G9,1,1)=1),NOT(MID(I9,1,1)=1),NOT(MID(J9,1,1)=1)),INT(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))*24+HOUR(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))+ROUND(MINUTE(((LEFT(G9,1)&":"&RIGHT(G9,2))-(LEFT(F9,1)&":"&RIGHT(F9,2)))+((LEFT(J9,1)&":"&RIGHT(J9,2))-(LEFT(I9,1)&":"&RIGHT(I9,2))))/60,2),""))))))))))))))))))

As you can probably guess, while that giant code works in Google Sheets (although it very sadly produced slightly off numbers, much to my dismay), it wasn't even accepted by Excel 2016 for being way too large. At this time, I'm stuck on this problem. I would much prefer to have an elegant solution, if one exists, so that I don't have to load a humongous code into my cells and abuse resources. I've spent a few days Google searching and trying to find some sort of clue as to why the calculated numbers are different in Excel 2016, but I'm just as clueless now as I was when I first started researching this. If I'm showing off my Excel ignorance really hard here, I've only just started using it for the very first time within the last 5 days, so I'm a real noob.

I apologize for explaining things at such length, but I wanted to make sure I was thorough in explaining my problem and the process I've used to attempt to solve it on my own. I will be very appreciative of any help or suggestions that can lead me in the right direction. I'm not above copy/pasting somebody's solution if it works, as this project has a deadline far more important than my need to learn Excel, and it's not for a class, so I'm not cheating.

Thanks for taking the time to look over this problem and assist me with your expertise.

Pecoflyer · #2 03-21-2017, 11:06 AM

I must admit I did not read the entire explanation
Does the attached put you on track?

xor · #3 03-21-2017, 11:29 AM

Maybe you can use (partly or wholly) the attached

trevorc · #4 03-21-2017, 11:51 AM

Check out this sample, uses autocalc and conditional formatting to provide correct totals, red squares go away only when everything is correct.

gluonman · #5 03-21-2017, 01:13 PM

Thank you so much, PecoFlyer, xor, and trevorc for your prompt replies. I'm going to test out each of these solutions and then let you know what ends up working for me. If none of them work out of the box, they may at least be a good starting point for me to refine the solution to my needs. And no worries about not reading the entire thing, PecoFlyer, I knew I was being terribly verbose.

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.

xor · #7 03-21-2017, 11:35 PM

[QUOTE=gluonman;112658]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.

That's fine.
It may not at all be a problem, but be aware that your preferred solution doesn't allow for times to cross midnight (for example 2200, 2300 2330 and 130 in A1, B1, C1 and D1 respectively).
(and the decimal values problem is just to multiply the value in K9 with 24 and format K9 as General).

gluonman · #8 03-22-2017, 04:58 PM

Quote:

That's fine.
It may not at all be a problem, but be aware that your preferred solution doesn't allow for times to cross midnight (for example 2200, 2300 2330 and 130 in A1, B1, C1 and D1 respectively).
(and the decimal values problem is just to multiply the value in K9 with 24 and format K9 as General).

Thanks for the additional tip. I had failed to consider that case, and turns out that will be a problem for me, so I'm going to see about modifying my solution a bit more to account for that.

xor · #9 03-22-2017, 08:26 PM

You are aware that the solution I provided does account for the crossing midnight problem?

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