Excel: adding / subtracting times, and "negative time"

[MasterKaos]

I'm trying to create a spreadsheet to keep track of horus worked, to see each day that i have worked more / less than the standard working day (7.5 hrs)

For example:

Start Time ¦ Break ¦ End Time ¦ Overtime
09:45      ¦ 0:45  ¦ 17:45    ¦ -0:15
09:00      ¦ 1:00  ¦ 18:00    ¦  0:30

Then i could do a sum of the overtime to see if i am over / under time for the whole week. In the aboce example i am 15 mins overtime.

I can't find any way to do this, because i get ####### when the calulcation results in "negative time"

What cell formatting and formulas should i use to achieve what i need?

----------------------------------------------------------------------------
The first 90% of the code accounts for the first 90% of the development time. The remaining 10% of the code accounts for the other 90% of the development time.

 

[GlennUK]

If you are going to be summing the Overtime column, then you could change your date system to the 1904 date system ( menu option Tools/Options/Calculation/ and tick the "1904 Date System" check-box ), which allows negative times to be displayed.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[MasterKaos]

Thanks GlennUK!

Works wonderfuly.

Just one more question, is it possible to input the time / min seperator as a dot rather than a colon? I've set the formatting on the column to be hh.mm but if i type in a dot it tries to do some calculation on it or something.
The idea is i could enter data a LOT quicker by using only the num pad rather than having to reach over to the colon all the time...

----------------------------------------------------------------------------
The first 90% of the code accounts for the first 90% of the development time. The remaining 10% of the code accounts for the other 90% of the development time.

 

[pbrodsky]

input the time as 4 digits - no delimiter. Then you can use a formula such as "=TIMEVALUE(LEFT(TEXT(B7,"0000"),2) & ":" & RIGHT(TEXT(B7,"0000"),2))" to convert it into an actual time value.

 

[SkipVought]

When you enter 11.45, Excel takes the CHARACTERS that you entered and analyzes the data making some built-in assumptions that include Regional Settings.

ONE decimal point, along with ALL numeric CHARACTERS: Excel assumes you're entering a NUMBER and CONVERTS the TEXT that you entered to a NUMBER, and DISPLAYS the number in some NUMERIC FORMAT.

More than ONE decimal point, along with ALL numeric CHARACTERS: Excel assumes you're entering TEXT.

Enter 11:45: Excel assumes you're entering TIME and COMVERTS the TEXT to Date/Time {11/24 + 45/60/24 which is 0.489583333) and DISPLAYS that NUMBER in a TIME FORMAT.

Short answer: NO!


Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[Infinity306]

Seems you could just use Decimals for the time for the function you are wanting... I.E 17.75 instead of 17:45, as long as you are rounding to the nearest quarter for time should work....and you could use decimals..or am I missing something?

 

[SkipVought]

BTW,

If you don't want to change the Date Syetem, you could always TEST the difference...
[tt]
=if(calculation<0,calculation*-1,calculation)
=if(calculation<0,-1,1)
[/tt]
in two adjacent cells.

Just a thot.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SkipVought]

Second thots...

I'd use
[tt]
=if(iserror(calculation),reverse the calculation,calculation)
[/tt]

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]