1

This may seem like a duplicate but my issue is different from others in the past. I've even tried the solution posted in a similar question from 9 years ago, and it still isn't working.

I am fixing my bosses time sheets she makes to post on the wall, and I can't separate the start and end times into their own cells. I know that would be easier and simple but this is not how she wants it, so I must leave the design the way she has it.

I'm wanting the little numbers in the screenshot that are above the times to display the time difference. The total time on the far right already does the math of those little numbers. I'm just wanting to give her the new Excel document and all she has to change is the hours and let the math do all the other work for her.

So when the hours are typed in the single cell as "7:00-3:30" the cell above it will calculate it and display "8" and I'm hoping that will work so if the person's start and end time were both a pm time then it will still work.

I know that's bad math, I'm taking out the 30-min lunch break that everyone gets if the total work daytime difference is longer than a 6-hour work shift.

screenshot

File --> https://easyupload.io/m3xbos

Here is the example I'm working with just to give you the idea of what it looks like, but the file I have provided above isn't the actual schedule. It's just a test sample file without all the clutter. I've even added my conversation I had with ChatGPT that was kind of helping me with my issue.

AlexWillis21
  • 113

2 Answers2

3

This formula assumes that the time will always be in the format x:xx and won't ever be something like 7:30-11. It is big and a bit messy, but it is essentially the same basic building block repeated a few times to account for different scenarios.

    =IF(A1="OFF",0,(IF(IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1)>0.25,
IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1)-(0.5/24),
IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1)))*24)

I'll try and break this down into a few simpler pieces.

First of all check to see if the time is "OFF", if it is put in 0 otherwise do the ugly looking maths:

=IF(A1="OFF",0,{long and ugly, but simple stuff})

This works out the time after the hyphen:

RIGHT(A1,LEN(A1)-FIND("-",A1))*1

This works out the time before the hyphen:

LEFT(A1,FIND("-",A1)-1)*1

These are then combined but with a check that if the right time component is smaller than the left then do "Right + 0.5 - Left" (12 hours is 0.5 of a day) otherwise do "Right - Left":

IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1)

This then has another IF wrapper around it saying if the number of hours > 0.25 (6 hours is 0.25 of a day) then do the (R-L or R+0.5-L) but knock off another half an hour (0.5/24), otherwise leave it as the (R-L or R+0.5-L). After this multiply whichever outcome it is by 24 to go from a fraction of a day to a whole number of hours:

IF(IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1)>0.25,
IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1)-(0.5/24),
IF(RIGHT(A1,LEN(A1)-FIND("-",A1))*1<LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1+0.5-LEFT(A1,FIND("-",A1)-1)*1,RIGHT(A1,LEN(A1)-FIND("-",A1))*1-LEFT(A1,FIND("-",A1)-1)*1))*24
RickyTillson
  • 467
0

Here is a formula you can use if you've Excel 365 and have LET() function available:

=LET(cell, A1, sep, FIND("-",cell), from, TRIM(LEFT(cell,sep-1)), to, TRIM(RIGHT(cell,LEN(cell)-sep)), gross, MOD((TIMEVALUE(to)- TIMEVALUE(from))*24,12), gross - IF(gross>6,0.5,0))

Just need to change cell reference ("A1")

It's possible to do the same also without LET(), however that case formula would be much longer due to many repetitions

enter image description here

Giacomo1968
  • 58,727
Máté Juhász
  • 22,071