Negative time cell formats

S

Steve

Hi

I have a excel worksheet which we use as a timesheet. The
users can carry over upto an hour negative each month.
But I am having problems with the negative cell format.
Can anybody help.

Thanks

Steve
 
N

Norman Harker

Hi Steve!

Excel uses two (alternative) time systems.

The first used as the default by Microsoft Excel for Windows is based
on 1 = 1-Jan-1900
The second used as the default by Microsoft Excel for the Macintosh is
based on 1 = 1-Jan-1904

Both use the decimal part of the number to represent time.

The first basis is the default time system and does not allow negative
times. The second does allow negative formats.

In Excel for Windows you can change to the 1904 system using:

Tools > Options > Calculation
Check 1904 Date System

The setting applies to each workbook separately.
Once selected you should not attempt to mix systems for that workbook.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
S

Steve

Hi Norman

Thanks for the suggestion but it did not work.

We are using Excel 2k for windows.
The formula we are using is [hh]:mm

But we still can not put in a negetive figure.

any suggestions??

Thanks

Steve
 
N

Norman Harker

Hi Steve!

I've set the 1904 date system in Excel 2000
I format the cell with custome format [hh]mm
I enter =-1/24
Returns -01:00

Try and report back if. Give an example if you still get a problem.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Steve said:
Hi Norman

Thanks for the suggestion but it did not work.

We are using Excel 2k for windows.
The formula we are using is [hh]:mm

But we still can not put in a negetive figure.

any suggestions??

Thanks

Steve








-----Original Message-----
Hi Steve!

Excel uses two (alternative) time systems.

The first used as the default by Microsoft Excel for Windows is based
on 1 = 1-Jan-1900
The second used as the default by Microsoft Excel for the Macintosh is
based on 1 = 1-Jan-1904

Both use the decimal part of the number to represent time.

The first basis is the default time system and does not allow negative
times. The second does allow negative formats.

In Excel for Windows you can change to the 1904 system using:

Tools > Options > Calculation
Check 1904 Date System

The setting applies to each workbook separately.
Once selected you should not attempt to mix systems for that workbook.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.



.
Click to expand...
Click to expand...
 
N

Norman Harker

Hi Steve!

Off group you revealed that you wanted to use an entry of -00:25

This was not accepted irrespective of the date system used.

Three alternatives are acceptable (in all cases the format is [hh]:mm)

=(1/(24*60))*25

=-TIME(0,25,0)

=-"00:25"

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Steve said:
Hi Norman

Thanks for the suggestion but it did not work.

We are using Excel 2k for windows.
The formula we are using is [hh]:mm

But we still can not put in a negetive figure.

any suggestions??

Thanks

Steve








-----Original Message-----
Hi Steve!

Excel uses two (alternative) time systems.

The first used as the default by Microsoft Excel for Windows is based
on 1 = 1-Jan-1900
The second used as the default by Microsoft Excel for the Macintosh is
based on 1 = 1-Jan-1904

Both use the decimal part of the number to represent time.

The first basis is the default time system and does not allow negative
times. The second does allow negative formats.

In Excel for Windows you can change to the 1904 system using:

Tools > Options > Calculation
Check 1904 Date System

The setting applies to each workbook separately.
Once selected you should not attempt to mix systems for that workbook.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.



.
Click to expand...
Click to expand...
 
Top