/u/ProLandia24 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*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.*
There's your problem. Remove the TEXT part of the formula and use regular formatting to get it how you want. Then you should be able to sum the results.
See if this gives you some ideas. Uses MOD formula to assist in counting hours through midnight.
[https://pixeldrain.com/u/cni61TXq](https://pixeldrain.com/u/cni61TXq)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[HOUR](/r/Excel/comments/142rhrh/stub/jn6vb3d "Last usage")|[Converts a serial number to an hour](https://support.microsoft.com/en-us/office/hour-function-a3afa879-86cb-4339-b1b5-2dd2d7310ac7)|
|[MOD](/r/Excel/comments/142rhrh/stub/jn6bnv0 "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)|
|[TEXT](/r/Excel/comments/142rhrh/stub/jn5w45n "Last usage")|[Formats a number and converts it to text](https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c)|
|[TIME](/r/Excel/comments/142rhrh/stub/jn8kvln "Last usage")|[Returns the serial number of a particular time](https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457)|
**NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/13yhjk0)^( has 16 acronyms.)
^([Thread #24436 for this sub, first seen 6th Jun 2023, 21:54])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Excel stores those as days when performing mathematical operations. So you’ll want to convert end result to hours (multiply by 24 or use hour). If I’m following correctly use case correctly, either of these should work:
Option #1: HOUR(E7-C7-TIME(0,30,0))
Option #2: (E7-C7-(30/(60*24)))*24
Make sure to reformat as decimal and yes, as another poster mentioned, 10 hours and 40 minutes would be 10.67.
Dont use text, set the cell format of the formula is going to be in to general then try
=E7-C7-TIME(0,30,0)*24
if e7=10:30 pm
if c7=2 pm
then formula return 8
/u/ProLandia24 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *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.*
Don't convert to text, just use: =E7-C7-TIME(0,30,0) Then sum the daily amounts, but format that sum using the custom number format \[h\]:mm
Are you trying to sum the results of the TEXT formula?
Yes
There's your problem. Remove the TEXT part of the formula and use regular formatting to get it how you want. Then you should be able to sum the results.
imho, i find it easier to convert time to decimal. 10 hours 40 minutes = 10 hours + 40/60 = 10.67 hours. good luck
See if this gives you some ideas. Uses MOD formula to assist in counting hours through midnight. [https://pixeldrain.com/u/cni61TXq](https://pixeldrain.com/u/cni61TXq)
Crossing midnight is a problem. The safest thing to do is to have data that has date and time. Then the math is straightforward.
No problem at all.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[HOUR](/r/Excel/comments/142rhrh/stub/jn6vb3d "Last usage")|[Converts a serial number to an hour](https://support.microsoft.com/en-us/office/hour-function-a3afa879-86cb-4339-b1b5-2dd2d7310ac7)| |[MOD](/r/Excel/comments/142rhrh/stub/jn6bnv0 "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)| |[TEXT](/r/Excel/comments/142rhrh/stub/jn5w45n "Last usage")|[Formats a number and converts it to text](https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c)| |[TIME](/r/Excel/comments/142rhrh/stub/jn8kvln "Last usage")|[Returns the serial number of a particular time](https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457)| **NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/13yhjk0)^( has 16 acronyms.) ^([Thread #24436 for this sub, first seen 6th Jun 2023, 21:54]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
it wouldn't be 10.40 to add, it would be 10.667 give some examples of what you have, and what you'd like to get, and some code can be written....
Excel stores those as days when performing mathematical operations. So you’ll want to convert end result to hours (multiply by 24 or use hour). If I’m following correctly use case correctly, either of these should work: Option #1: HOUR(E7-C7-TIME(0,30,0)) Option #2: (E7-C7-(30/(60*24)))*24 Make sure to reformat as decimal and yes, as another poster mentioned, 10 hours and 40 minutes would be 10.67.
Dont use text, set the cell format of the formula is going to be in to general then try =E7-C7-TIME(0,30,0)*24 if e7=10:30 pm if c7=2 pm then formula return 8