Negative time hh:mm entry and calculations

Deuterophobia

New Member
Joined
Sep 23, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello. I know that Excel cannot deal with negative hh:mm without serious workarounds. I am trying to create a sheet where an employee's time over or under the required amount per week can be entered, and then the difference calculated automatically.

At present, the only way I am currently aware of entering negative hh:mm is to prefix it with an apostrophe (or format as text) and this renders the cell contents non-mathematical. I am wondering if it is worthwhile attempting to create a formula, presumably with a large number of nested IF & using AND or if this is too ambitious. I suspect I could write a macro in VBA to do this, but a pure spreadsheet with formulas/functions would be preferable.

I am familiar with using LEFT to test if the two cells are prefixed with a minus sign, and using MID to obtain the ABS, and then to convert to a decimal for the calculation in an extra column. It's the level of nested IF &c which is causing some alarm.

The calculation would have to cater for both times being positive; one negative and the other positive; one positive and the other negative; both negative. If both negative, it would have to consider if the first number is larger or smaller than the second number.

I'm not necessarily looking for a complete answer; just some hints, including DON'T BOTHER if this is a futile endeavour!

Many thanks for your time.
 

Attachments

  • Model.jpg
    Model.jpg
    26.7 KB · Views: 5

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
First off, and do this and just for this workbook.
When this workbook is open, go into File>Options>Advanced
Scroll down to this section, and click Use 1904 Date System.

1727110331621.png


Now in your sheet add a column after each week that you can put an X in to denote negative time.

Book1.xlsx
ABCDEFGHI
1WEEK1NWEEK2NWEEK3NWEEK4NTOTAL
25:00X8:004:00X3:00x-4:00
Sheet1
Cell Formulas
RangeFormula
I2I2=SUM(TOROW(IF(B2:H2="X",-1,1)*A2:G2,3))
 
Upvote 0
Solution
First off, and do this and just for this workbook.
When this workbook is open, go into File>Options>Advanced
Scroll down to this section, and click Use 1904 Date System.

View attachment 117205

Now in your sheet add a column after each week that you can put an X in to denote negative time.

Book1.xlsx
ABCDEFGHI
1WEEK1NWEEK2NWEEK3NWEEK4NTOTAL
25:00X8:004:00X3:00x-4:00
Sheet1
Cell Formulas
RangeFormula
I2I2=SUM(TOROW(IF(B2:H2="X",-1,1)*A2:G2,3))
Hi, Scott

Many thanks! I simply had not thought about doing it this way. It's WAY more manageable than the method I was considering.
 
Upvote 1

Deuterophobia,​

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this thread for you.
 
Upvote 0

Forum statistics

Threads
1,224,461
Messages
6,178,811
Members
452,880
Latest member
kares

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top