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
Start	Finish	Difference	Alloc	Decimal Time Difference

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]17:30[/TD]
[TD="align: right"]11:30[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]-3.5[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]0[/TD]

</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
Start	Finish	Difference	Alloc	Ahead of Alloc.	Behind Alloc
*
*
*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]2:00[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]17:30[/TD]
[TD="align: right"]11:30[/TD]
[TD="align: right"]8:00[/TD]

[TD="align: right"]3:30[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]8:00[/TD]

</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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Job No[/TD]
[TD]Time Spent on Job[/TD]
[TD]Time Allocated to Job[/TD]
[TD]Time Remaining[/TD]
[/TR]
[TR]
[TD]J00001[/TD]
[TD]56:00[/TD]
[TD]26:00[/TD]
[TD]-30:00[/TD]
[/TR]
[TR]
[TD]J00002[/TD]
[TD]18:30[/TD]
[TD]20:00[/TD]
[TD]1:30[/TD]
[/TR]
[TR]
[TD]J00003[/TD]
[TD]2:48[/TD]
[TD]3:00[/TD]
[TD]0:12[/TD]
[/TR]
</tbody>[/TABLE]

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
Job No	Time Spent on Job	Time Allocated to Job	Time Remaining
J00001	-30:00
J00002
J00003

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8:00[/TD]
[TD="align: right"]2:00[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]18:30[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: right"]1:30[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2:48[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]0:12[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=IF(C2>B2, C2-B2,"-" & TEXT(B2-C2,"[h]:mm"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


If you change to the 1904 system for the WB, the format you need is
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]+[h]:mm;-[h]:mm[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[DrunknMunky14]

Thanks mate :D