![]() If you are using the 1900 date system, you could construct a text string to display the results doing something like: If you are using the 1904 date system, you can just have your formula =B1-A1 ***HOWEVER*** unless you are using the 1904 date system (standard on Mac's optional on PC's), Excel will not display the negative time, in time format rather it will display a string of #'s If you want to have a negative time result if you work less, you need to always be subtracting the time you are supposed to work (column 1), from the time you actually worked (in column 2). In your formula, you are always going to be subtracting the smaller value from the larger because of the IF clause. Is it +19 when you expect -19? Or is it a string of #'s. You don't indicate what wrong answer you are getting. If not, let us know your needs, and we might be able to offer negative time _text_) in sums and other arithmetic The only problem: you will not be able to include that "negative =IF(B2"&TEXT(A2-B2 ":mm") B2-A2)Īnd you might want to set the Horizontal Alignment format to Right. Exactly what to do depends on your requirements. You only need some mechanism for distinguish "worked more" and "work To work around that, you always want the result to be non-negative. The problem is: Excel is not happy with negative values using a timeįormat (Time or Custom :mm). Likewise, you get a negative result indicating that you worked less. (A2), you get a positive result indicating that you worked more. Thus, if actual time worked (B2) is more than expected time worked Perhaps you do not understand): it always returns non-negative time The IF expression is doing what you told it to do (but I expect "-00:19" minutes, a negative time, indicating that I > But if I work less then 8:24 on a day, then the formula gives me an > In B1 I have the time I worked on a given day e.g.: 08:30 in A1 I have the time I have to work every day: 08:24 / HH:mm
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |