A formula in the Excel working hours calculator doesn't go into the negative, does anyone know why?
Hello everyone
I created a new working hours calculator last week because my employee's was incorrect. However, mine only shows overtime hours if they're in the black; otherwise, it just shows "#####". The formula is: =IF(B1="";"";B5-$A$8/5).
B1 is when I arrived for work, B5 is the working time per day and A8 is the total target time per week.
If anyone can help me, I would be very grateful 🙂
(1 votes)
Loading...
That’s how it works
Excel – File options – extended 1904 hook set
It’s just the problem of display.
Calculatively calculates Excel also for Dir, if you recognize this minus time (#######) with a plus time You, if the result is positive.
Thanks for your answer, now it works:)
Barley
Thanks for ⭐
You can achieve this through the Excel Format 1904, but I would strongly advise from this format, as you may add the entire table, as all date values jump forward by +4 years.
I would therefore convert the time the Excelintern from 0-1 for 0 to 24 o’clock just in hours so *24
e.g. A1: 08:00 B1: 14:00
=(A1-B1)*24 >> -6 as numerical format must logically be number and not time.
If you only want a display, you can also add a minus manually, e.g.
If you want to continue with the value, you would have to convert a number again, e.g. by multiplying *1
Because there is no negative time. If you convert your times in hours, for example, you will come to a result.
This is already the case and yet it does not work. Therefore, my confusion.
Doesn’t look like that for me on the picture. I only see times, for example 07:46. In hours, this would be 7.76.
Now yes 😉
I found a way to make a number from a time:
Apply this formula to a cell with time and format the cell itself as a number –> =STUNDE(A1)+(MINUTE(A1)/60)
Then you get a number or Time value from your time. And with these values you can come to the negative range.
Standard or number. However, I do not currently have a way to convert a time directly to a number via Excel if you have.
What would that be? Right now it’s hh:mm.
You somehow didn’t understand what a time is in Excel, and that’s why your computer doesn’t get nonsense when you calculate times at 42/5 (also where you mean the positive result “overtime” is still correct).
Time in Excel is one fraction of 1 – a number between 0 and 1, where 0 corresponds to the time of 00:00, 0.25 would then be 06:00 and 0.5 is 12:00 noon. The number 0.75 is then 18:00 in the evening and 1 equals 24:00
If you take off the number from 07:39 (= 0.31875) the number 42/5 = 8.4, you will get the number -8,08125. If then the cell as time or Date formatted, this makes little sense because the number is in front of the Excel time bill that starts with the 1.1.1900 (date is then at some point on 23.12.1899)
Other: To make a real number of hours from a time difference or time sum, you must multiply the time difference/sum by 24 . In your formula:
Then there would be -0.75 hours in the absence of time (42/4 hours =8.4 hours are 8 hours and 24 minutes daily working time and then there would be a lack of 45 minutes = 0.75 hours on the desired working time at 7 hours 39 minutes working time)
In the end, a work-time recording with Excel & Co is extremely complex, if you want to work with times or differences of time/muss – especially if day changes (e.g. in shift operation) are still in play.
Thank you for your answer, unfortunately this is not so true. I have a nominal working time of 8:24 excl. Break. As you can see at one picture, I’ve done 4 minutes of sliding time and that’s true. I compared it extra to the SAP data to make sure. :
Then it’s not true and I wish Have a lot of fun calculating overtime and times with your own understanding of times in Excel.
I don’t think you’re wrong, but the questioner doesn’t expect 42:00, so the 42 is already converted into the original format.
I still prefer the calculation with decimal numbers because it is less susceptible to errors. In particular, also because it does not have to constantly pay attention to negative results and this has to be caught. But only once in the end, if you find the hourly representation more beautiful.
So I would count in decimal and at the end convert in Excel time, if necessary add a front, see the formula in my post.
I will. I wish you a wonderful working day.