Obtaining a decimal total hours from military time entries (my solution works only in Google Sheets)
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.
|