Negative time hh:mm entry and calculations

[Deuterophobia]

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.

 

[Scott Huish]

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.

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

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	WEEK1	N	WEEK2	N	WEEK3	N	WEEK4	N	TOTAL
2	5:00	X	8:00		4:00	X	3:00	x	-4:00
Sheet1

Cell Formulas
Range	Formula
I2	I2	=SUM(TOROW(IF(B2:H2="X",-1,1)*A2:G2,3))

 

[Deuterophobia]

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
	A	B	C	D	E	F	G	H	I
1	WEEK1	N	WEEK2	N	WEEK3	N	WEEK4	N	TOTAL
2	5:00	X	8:00		4:00	X	3:00	x	-4:00
Sheet1

Cell Formulas
Range	Formula
I2	I2	=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.

 

[Joe4]

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.