Time difference when times are in one cell

Question

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.

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.

1. The image should be inline. 2. " "7:00-3:30" = 8??? what sort of math is that? — DrMoishe Pippik, Feb 26 '23 at 17:27
@DrMoishePippik haha sorry I explained that math only in my head, :P it's with the 30-min lunch taken out. But the image isn't really relevant. The point was the math to the time in the cell and have it output a time difference from the numbers provided in the single cell. Did you look at the file, I think it will make more sense if you look at the file than the image? — AlexWillis21, Feb 26 '23 at 17:33
Clicking the file link results `The transfer you requested has been deleted.` — Peregrino69, Feb 26 '23 at 22:46
@Peregrino69 I'm sorry I tried another file sharing site and this time I tested it. I hope you are able to get it and help me with my issue. Thanks. — AlexWillis21, Feb 27 '23 at 02:53
Excel's not my strong suit. Personally I'd just explain my manager why it's easier to use 2 cells... I think you'd find the answer here: https://superuser.com/questions/253353/excel-function-that-evaluates-a-string-as-if-it-were-a-formula — Peregrino69, Feb 27 '23 at 07:34
Use a function to split the string, [convert the string](https://superuser.com/questions/51887/convert-excel-text-string-to-time). Do the usual math (right part minus left part minus 30 minutes). — Seth, Feb 27 '23 at 08:20
Please edit your question: When you say this, “I've even tried the solution posted in a similar question from 9 years ago.” What is the exact URL of the similar question you are mentioning? None of us keep deep track of each question here. Better to see what you tried and might have not worked than just second-guessing what you did. — Giacomo1968, Mar 02 '23 at 02:18

score 3 · Accepted Answer · answered Feb 27 '23 at 15:06

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

Thank you for explaining this and breaking it down the way you did. Now I'm hoping my manager doesn't mess it up over time. — AlexWillis21, Mar 02 '23 at 01:49

score 0 · Answer 2 · edited Mar 02 '23 at 02:20

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

Time difference when times are in one cell

2 Answers2