Creating a time sheet with time calculation

[DrunknMunky14]

Hi all,
Been a while since I last posted here.

I'm trying to create a time sheet which takes hours and minutes but not the time.
For example, a job takes 18 hours and 23 minutes and come out of a CSV file as 18:23, but the allocated time is only 15 hours (shown as 15:00), I want to be able to get -3 hours and 23 over the allocated time as -3:23.

Right now I'm getting thrown a #VALUE! error because time can't be negative.

What is the right cell format to achieve this?

 

[Steve_R]

If you change the format of the negative value to a number you will see a negative number that can be used in a calculation but that's not very attractive.

Alternatives
Convert to decimal time and use that to do the calculations.

Sheet1

*	A	B	C	D	E
1	Start	Finish	Difference	Alloc	Decimal Time Difference
2	6:00	14:00	8:00	10:00	2
3	6:00	17:30	11:30	8:00	-3.5
4	6:00	14:00	8:00	8:00	0

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:73px;"><col style="width:64px;"><col style="width:92px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E2 =(INT(D2)*24+HOUR(D2)+ROUND(MINUTE(D2)/60,2))-(INT(C2)*24+HOUR(C2)+ROUND(MINUTE(C2)/60,2)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



Another method is to have the positive values in one column and the values that should be negative in the other as follows (Note on-schedule is blank in both columns).


Sheet1

*	A	B	C	D	E	F
1	Start	Finish	Difference	Alloc	Ahead of Alloc.	Behind Alloc
2	6:00	14:00	8:00	10:00	2:00	*
3	6:00	17:30	11:30	8:00	*	3:30
4	6:00	14:00	8:00	8:00	*	*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E2 =IF(D2>C2,D2-C2,"") F2 =IF(C2>D2,C2-D2,"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[DrunknMunky14]

Hi Steve,

That isn't exactly what I'm after.

I'm using hours and minutes, not in a 24 format, but more as a numerical format.

Job No	Time Spent on Job	Time Allocated to Job	Time Remaining
J00001	56:00	26:00	-30:00
J00002	18:30	20:00	1:30
J00003	2:48	3:00	0:12

<tbody>
</tbody>

The bold is what I'm trying to get from a formula. All cells are in a hh:mm style and that's what I'm given from the CSV file. They can be changed to hh:mm:ss if needed.

 

[DrunknMunky14]

Don't worry, just found out how to do it. I had to change my calculation settings to using 1904 date system.

 

[DrunknMunky14]

Nevermind, it just broke everything... Thought it worked

 

[Steve_R]

Sorry, I thought I posted another option after that that gives you what you want but there is a catch.

This one gives the display you want but the negative time display is a string (so Excel aligns if left until you change it). The catch is that value cannot be used in subsequent calculations without more work:

Excel 2010

	A	B	C	D
1	Job No	Time Spent on Job	Time Allocated to Job	Time Remaining
2	J00001	8:00	2:00	-30:00
3	J00002	18:30	20:00	1:30
4	J00003	2:48	3:00	0:12

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =IF(C2>B2, C2-B2,"-" & TEXT(B2-C2,"[h]:mm")) <tbody> </tbody>

<tbody>
</tbody>

If you change to the 1904 system for the WB, the format you need is

+[h]:mm;-[h]:mm

<tbody>
</tbody>

But changing to 1904 system will change any dates in the WB.

 

[DrunknMunky14]

Thanks mate :D