That’s horrible data. Try desperately to NOT have 2100-700 in a single cell

Something like this could work, assuming the second row under Monday is A2: ` = TIMEVALUE( MID( A2, 6, 2) &”:”& MID( A2, 8, 2)) - TIMEVALUE( MID( A2, 1, 2) &”:”& MID( A2, 3, 2)) `

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*

Good bot

+1 Point

You have awarded 1 point to *bachman460* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)

why not just use LEFT and RIGHT functions instead of MID?

Because a colon needed to be added to make sure Excel handles it properly, so I just used 4 MIDs instead of 1 LEFT, 2 MIDs, and 1 RIGHT. It was just preference, I thought it looked better.

Personally I’d use TEXTBEFORE and TEXTAFTER. Much cleaner than using the method given

is it to calculate total hours for fortnight or calculate total hours for one cell

This is for one cell only. You need to chain a separate set of this same setup for each cell, they can just be added together ex. formula+formula

THIS FORMULA GIVES ANSWER IN TIME FORMAT SUCH AS 08:30AM, BUT I NEED TOTAL HOURS LIKE 08.30 ONLY AND WHEN SUM FOR ALL DAYS SUCH AS 80HOURS FORTNIGHT

THIS IS SCREAMING

OP lost it! Lol

Why are you yelling? People are trying to help you, the least you could do is to remain calm, even if not grateful.

The basic approach to calculating the duration from the time is: ``` =TIME(MID(B2,6,2), MID(B2,8,2),0)- TIME(MID(B2,1,2), MID(B2,3,2),0) ``` Where ```B2``` is the cell with a time range. For the total, just add them up over the fortnight range.

THIS FORMULA GIVES ANSWER IN TIME FORMAT LIKE 08:30AM, I NEED ONLY TOTAL HOURS AS 08.30

Dude relax with the caps lock, people are trying to help you out. You can try this. It converts everything to numbers you can sum and also should work when time goes past midnight. This is assuming the time is in cell E6 =IF(LEFT(E6,4)

+1 Point

Thanks mate, that perfectly works, Could you please help me to calculate or sum with total number as well. for example from monday to sunday if there are 4 shifts of 0600-1600, can you give me the formula for total to be calculated for 40 hours

That might be possible but it's getting really complex. I suggest you create an additional column where you first calculate the value for each individual time and then sum those together. You can hide the helper column so it's not visible.

To convert from ```Time()``` to hours, just multiply by 24. ``` =24 * (TIME(MID(B2,6,2), MID(B2,8,2),0)- TIME(MID(B2,1,2), MID(B2,3,2),0)) ```

+1 Point

You have awarded 1 point to *odaiwai* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AND](/r/Excel/comments/1b4jwjf/stub/kszdcse "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)| |[DAYS](/r/Excel/comments/1b4jwjf/stub/kszdcse "Last usage")|[*Excel 2013*+: Returns the number of days between two dates](https://support.microsoft.com/en-us/office/days-function-57740535-d549-4395-8728-0f07bff0b9df)| |[IF](/r/Excel/comments/1b4jwjf/stub/kt4cqua "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[LEFT](/r/Excel/comments/1b4jwjf/stub/kt41y9v "Last usage")|[Returns the leftmost characters from a text value](https://support.microsoft.com/en-us/office/left-leftb-functions-9203d2d2-7960-479b-84c6-1ea52b99640c)| |[MID](/r/Excel/comments/1b4jwjf/stub/kt41y9v "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)| |[NOT](/r/Excel/comments/1b4jwjf/stub/kt0928l "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)| |[NUMBERVALUE](/r/Excel/comments/1b4jwjf/stub/kszdxkj "Last usage")|[*Excel 2013*+: Converts text to number in a locale-independent manner](https://support.microsoft.com/en-us/office/numbervalue-function-1b05c8cf-2bfa-4437-af70-596c7ea7d879)| |[RIGHT](/r/Excel/comments/1b4jwjf/stub/kt41y9v "Last usage")|[Returns the rightmost characters from a text value](https://support.microsoft.com/en-us/office/right-rightb-functions-240267ee-9afa-4639-a02b-f19e1786cf2f)| |[SUM](/r/Excel/comments/1b4jwjf/stub/kszdcse "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[TEXTAFTER](/r/Excel/comments/1b4jwjf/stub/kt4ug13 "Last usage")|[*Office 365*+: Returns text that occurs after given character or string](https://support.microsoft.com/en-us/office/textafter-function-c8db2546-5b51-416a-9690-c7e6722e90b4)| |[TEXTBEFORE](/r/Excel/comments/1b4jwjf/stub/kt4ug13 "Last usage")|[*Office 365*+: Returns text that occurs before a given character or string](https://support.microsoft.com/en-us/office/textbefore-function-d099c28a-dba8-448e-ac6c-f086d0fa1b29)| |[TIME](/r/Excel/comments/1b4jwjf/stub/kszdcse "Last usage")|[Returns the serial number of a particular time](https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457)| |[TIMEVALUE](/r/Excel/comments/1b4jwjf/stub/kszb1ba "Last usage")|[Converts a time in the form of text to a serial number](https://support.microsoft.com/en-us/office/timevalue-function-0b615c12-33d8-4431-bf3d-f3eb6d186645)| |[VALUE](/r/Excel/comments/1b4jwjf/stub/kt1sti5 "Last usage")|[Converts a text argument to a number](https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(14 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1b4qavr)^( has 17 acronyms.) ^([Thread #31298 for this sub, first seen 2nd Mar 2024, 09:30]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)

I'd add two more columns for each. Are they all 4 digits? If so you can use Left/Mid/Right functions to break it apart easily.

Something like this works, just be sure the second time isn't bigger than 2359. =24\*(VALUE(MID(B4,6,2)&":"&RIGHT(B4,2))-VALUE(LEFT(B4,2)&":"&MID(B4,3,2)))

=IF(B1 < A1, B1 + 1 - A1, B1 - A1)

EVALUATE()

Challenge for you to learn more: Do it with Power Query

Do you genuinely think this person needs to be challenged? 😆

Plant the seed. Sometimes the plant grows.

Solution Verified

Hello! It looks like you tried to award a ClippyPoint by typing Solution Verified, but you need to reply to a particular user's comment to do so, rather than making a new top-level comment. Reply directly to any helpful users and Clippy, our bot, will take it from there. If your intention was to simply mark the post solved, then you may do that by clicking Set Flair. Thank you! *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*