now another Question… to work out time.
Scenario one: guys started this a shift from (A1) 20h00 and ended the shift (B1) 23h00, Excel A2 minus A1 = 03h00, which is correct
Scenario two, guys started a shift at (A2) 20h00 and ended the shift at 06h00 (B2) (the next morning), excel cant calculate? the answer must be 10h00 hours
what is the easiest formula to do the necessary conversion, to work out the hours 100%?
A1) 20h00
You really want use text as a time value (or are you talking about using Format Code HH"h"MM )? Just my my 5ct advice: Use real calc date + time (datetime) as start and end values and every duration (!!!) in hours simply calculates as =A2-A1 formatted by [HH]:MM
When you are using Excel, it is wise to ask in a forum specialised on Microsoft software. This place is for LibreOffice, which is a different (albeit similar) software suite.
See techcommunity.microsoft.com
Many answers for Calc will also be suitable for Excel, but in some cases you will see different behavior. One case is with “negative time” which (as you discovered) Excel can’t handle properly.
#########
-14:00:00 (which is technically correct, but not the result you are after).For time calculations in spreadsheets, the unit is “day”. For both apps, when you have a timespan crossing midnight, you have to add 1 to the calculation to signify “one day later”. How you do that, depends on how you have organised your timesheet. It may appear in the “end time” input cell, a separate cell for “date count” if you have long shifts (several days), or in your formula (conditional “If end time is before start time add 1”) if shifts are always shorter than 24 hours.
you have to add 1 to the calculation to signify “one day later”.
Sure - however: Is it just a typo start time being before end time, or is it really next day or …? All these calculations and formulas are based on assumptions, if there is no date eliminating any uncertainty.
… All these calculations and formulas are based on assumptions, …
Indeed!
Data entry in spreadsheets is rather “free form”,
Also, if you have a cell formatted to show hours and minutes, and use the keyboard shortcut to enter “now” timestamp, there will be an invisible date component in there. In conjunction with a manually entered “simple time” value, you can get some interesting results…
Using spreadsheets for multiple user data entry is not recommended. You need to know what you are doing. You also sometimes need to know what has been done before. There are tools to ensure data integrity in spreadsheets, but they are easily defeated. Nothing is foolproof.