I have total hours worked in one row (see example below) with these values:
A1= 22:25, A2=-10:00, A3=3:00, A4=-4:45
(some time values are negative because employees didn't work the full shift).
The entries represent the hours and minutes works (e.g., A1: 22 hours 25 minutes)
So now all I want is to calculate total working hours like =SUM A1+A2+A3+A4= +-total working hours. However, the cells are formated as text because Excel doesn't allow entering negative time.
Now I need to somehow sum up rows (formated as time) and convert it to time h:mm.
Best Answer
Ignoring what underlies the actual numbers, you have negative times that you want to display properly as negative times and be able to use for time arithmetic. As workarounds, you tried formatting the times as text (complex to use in a math formula), and as decimal numbers (won't give the right answer unless you converted the minutes to hundredths of an hour). None of that is required.
The formatting issue is specific the the "1900 basis" for handling dates/times. The name comes from storing dates as the number of days since January 0, 1900. Dates are then an integer day count, and times are stored as decimal fractions of a day. So today at noon would be stored as 43286.5, and 3 hours is stored as 1/8th of a day, or 0.125. The stored numbers are formatted to display in the readable form of your choice.
Since dates and times are stored as a single number, you can use it like any other number to do arithmetic without worrying about time units or other factors. The 1900 basis happens to have a design restriction that doesn't allow negative times.
There is a built-in option to use a "1904 basis", which doesn't have the restriction on negative times. That's actually the default on the Mac version. With that you can display negative times and use them in calculations. You can select it from the menu. The route there varies with Excel version. It is in the
Optionsdialog.In the 2007 version, it was via the Office button. In the 2010, 2013, 2016, and 365 versions, it was via the
Filetab. If the route has changed in newer versions, perhaps someone will be kind enough to add that to this answer. From the Options dialog, selectAdvanced. [One undated reference I just found that doesn't list the applicable version gives a route ofTools » Options » Calculation, so that's another one to try if the other routes don't match your version.]Check
Use 1904 date system, andOK.Now you can store and display negative times as negative times, and do time math simply. For example, just sum the times (regardless of positive or negative) to get the total.