-ve time calue

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

I am attempting to insert a negative time value into a
cell but it keeps reverting the time to a date/time value.
I know that excel will allow a negative time value if you
are doing a calculation however I just wish to enter the
value manually. The Cell is formatted to [h]:mm;[Red] -
([h]:mm).

Thanks
Aaron
 
You "know" you can enter negative time values? As in -9:00? That is a new
wrinkle on my horn, and if it can be done that way, I do not know of it but
am willing to learn, If you have time in a cell, such as 7:00 PM in A2, you
may use =A2-(2.5/24), 4:30 PM will be returned.
 
If you have your date system set for 1904 (tools|Options|Calculation tab), then
you can enter negative time.

But it'll affect every date in that workbook--so be careful.


You "know" you can enter negative time values? As in -9:00? That is a new
wrinkle on my horn, and if it can be done that way, I do not know of it but
am willing to learn, If you have time in a cell, such as 7:00 PM in A2, you
may use =A2-(2.5/24), 4:30 PM will be returned.

--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays
Aaron said:
I am attempting to insert a negative time value into a
cell but it keeps reverting the time to a date/time value.
I know that excel will allow a negative time value if you
are doing a calculation however I just wish to enter the
value manually. The Cell is formatted to [h]:mm;[Red] -
([h]:mm).

Thanks
Aaron
Click to expand...
Click to expand...
 
My apologies as it seems I have not explained myself
properly. I am trying to calculate overtime and
unfortunately I am starting with some -ve values (as a
person is starting off with negative 4 hours in over time).

We already have a formula for working out overtime as long
as it stays in the positive the problem being that some
people are already in the negative and excel will not
allow us to put in this value to the cell as it
automatically reverts the value to a date time.

ideal outcome would be
starting flex -9.5hrs (this is where the problem
lies)
flex accumulated 5hrs
flex taken 4hrs
total flex available -8.5hrs

Thanks
-----Original Message-----
You "know" you can enter negative time values? As in - 9:00? That is a new
wrinkle on my horn, and if it can be done that way, I do not know of it but
am willing to learn, If you have time in a cell, such as 7:00 PM in A2, you
may use =A2-(2.5/24), 4:30 PM will be returned.

--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays
I am attempting to insert a negative time value into a
cell but it keeps reverting the time to a date/time value.
I know that excel will allow a negative time value if you
are doing a calculation however I just wish to enter the
value manually. The Cell is formatted to [h]:mm;[Red] -
([h]:mm).

Thanks
Aaron
Click to expand...


.
Click to expand...
 
Already set as we already have -ve values when using
calculations...just will not allow me to manually put in a
negative value

Thanks
-----Original Message-----
If you have your date system set for 1904
Click to expand...
(tools|Options|Calculation tab), then
you can enter negative time.

But it'll affect every date in that workbook--so be careful.


You "know" you can enter negative time values? As in - 9:00? That is a new
wrinkle on my horn, and if it can be done that way, I do not know of it but
am willing to learn, If you have time in a cell, such as 7:00 PM in A2, you
may use =A2-(2.5/24), 4:30 PM will be returned.

--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays
I am attempting to insert a negative time value into a
cell but it keeps reverting the time to a date/time value.
I know that excel will allow a negative time value if you
are doing a calculation however I just wish to enter the
value manually. The Cell is formatted to [h]:mm; [Red] -
([h]:mm).

Thanks
Aaron
Click to expand...
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
Hi Aaron!

It's an unusual one!

With 1904 date system to enter a negative time:

-"09:30"

You'll have to format the result for time.

I'd repeat and stress Dave's warning about the 1904 date system!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I missed that you were entering the values manually:

I couldn't do:
-9:00:00

But I could do:
=-time(9,0,0)
or
=-timevalue("9:00:00")

My apologies as it seems I have not explained myself
properly. I am trying to calculate overtime and
unfortunately I am starting with some -ve values (as a
person is starting off with negative 4 hours in over time).

We already have a formula for working out overtime as long
as it stays in the positive the problem being that some
people are already in the negative and excel will not
allow us to put in this value to the cell as it
automatically reverts the value to a date time.

ideal outcome would be
starting flex -9.5hrs (this is where the problem
lies)
flex accumulated 5hrs
flex taken 4hrs
total flex available -8.5hrs

Thanks
-----Original Message-----
You "know" you can enter negative time values? As in - 9:00? That is a new
wrinkle on my horn, and if it can be done that way, I do not know of it but
am willing to learn, If you have time in a cell, such as 7:00 PM in A2, you
may use =A2-(2.5/24), 4:30 PM will be returned.

--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays
I am attempting to insert a negative time value into a
cell but it keeps reverting the time to a date/time value.
I know that excel will allow a negative time value if you
are doing a calculation however I just wish to enter the
value manually. The Cell is formatted to [h]:mm;[Red] -
([h]:mm).

Thanks
Aaron
Click to expand...


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