Excel subtract big numbers as hours and minutes
The subtracting 36716.09 from 28961.49 (36716.09 - 28961.49, ‘.’dot is separator represents minutes: 9 minutes and 49 respectively) yields 7754.60 Which accounts for 7755 hours.
However, the correct result is 7754.20, 7754 hours and 20 minutes.
36715 - 28961 =7754 hours 69 - 49 =20 minutes
I don’t have a particular preference to time format. The delimiter can be colon or any other separator as long as hours/minutes is one value, so can be allocated in one cell.
The values can be vary but do not exceed 7 digits, hhhhh.mm
I have tried different time/text functions/formulas and cell formats with no avail.
Could someone advise/point me on cause/solution of this error, please.
P.S. Thank you all of stackoverflow community for the provided solutions.
=(TRUNC(A1)/24+(A1-TRUNC(A1))/14.4)-(TRUNC(B1)/24+(B1-TRUNC(B1))/14.4)
Result custom formatted as [hh]:mm
Result:
- How to import values from excel with pandas into tkinter faster?
- Speed up concatenation of excel files with Pandas
- Trying to send screenshot of excel in an email
- Sum of text Does not contain in Range
- Stopping Excel Macro executution when pressing Esc won't work
- Consolidated Date window
- Excel formula for calculating difference between two date columns
- Get number of decimals in python float, including trailing zeros
- Excel VBA search for data and stuck at a certain point due to group or split source file?
- Convert Time Input in Decimal Format - Excel Formula or VBA
- Lookup unique "Values" if they follow a certain "Pattern" from a lookup table then return a "Name"
- Share office scripts connected to Excel with external organisations
- Tiling an Excel array in 2 dimensions, ideally not using VBA
- How to get the cartesian product of two sets via Excel Formulas
- Repeat Value for Every Instance of Another Value in Excel without using Power Query
- VSTACK doesn't pile up dynamic ranges
- How To Close a UserForm Properly in Excel VBA?
- In Excel, can I use a hyperlink to run vba macro?
- EXCEL: Count all unique values in Table column up to a certain point, and combine them into a single string?
- Excel VBA connection to SQL Server with Microsoft Entra MFA authentication
- Excel shows incorrect polynomial trendline function
- Split too large VBA into multiple Subs
- Trying to calculate user whos spent the most and the least - Excel
- How to do a union of two columns and then sort them into a third column in Excel?
- Is there a way to create a simple custom formula in Excel without VBA?
- Copying a String Array into a Range of cells (in MS Excel using VBA)
- vlookup with formula calculated from lookup cell location
- How to extract R InstallPath from Registry using Excel VBA
- How to inner-join in Excel (eg. using VLOOKUP)
- Scientific notation when importing from Excel in .Net