Time 0:00

[Wil Moosa]

I guess the answer must be somewhere... but I do not find it!

I want to make calculations with time. Actually I want to substract the value of one cell (0:00) from the value of another (22:00). The cell with the value 0:00 need to be read as 24:00. How van I easily calculate with the value 0:00?

 

[Aladin Akyurek]

=B1-A1+(B1<A1)

where A1 houses a time value like 22:00 and B1 0:00.

Time format the formula cell.

Is this what you wanted?
This message was edited by aladin akyurek on 2002-10-19 19:50

 

[Dave Patton]

On 2002-10-19 18:51, Wil Moosa wrote:
I guess the answer must be somewhere... but I do not find it!

I want to make calculations with time. Actually I want to substract the value of one cell (0:00) from the value of another (22:00). The cell with the value 0:00 need to be read as 24:00. How van I easily calculate with the value 0:00?

Enter 24:00 in A1 it will show as 0:00 Since it is 1 day
Enter 22:00 in A2

A3 =A1-A2 result 2:00

With default Time/Date settings for PC Excel you cannot do
=A2-A1 since that would yield a negative number.


If this does not help, please clarify your question.
This message was edited by dave patton on 2002-10-19 19:45

 

[Wil Moosa]

David, it seems to do what I want it to do... but I must say that I am surprised that it does.

What happening is that a time with date appears in the cells ( 1-1-1900 24:00:00 in a2 and 0-1-1900 24:00:00 in a1) I do understand why it selects these dates but it does not seem to follow mý logic.

It works for the moment (I am not 100% sure yet how it influences my total sheet and the outcome of all the calculations) but...

...is there a way to hide the date(s) so only the time can be seen (date is necessary for the right outcome but may confuse users of my sheet)?

One other thing is that 0:00 does not work, but 24:00 (what does not change into 0:00)does.
This message was edited by Wil Moosa on 2002-10-20 04:55
This message was edited by Wil Moosa on 2002-10-20 04:56

 

[plettieri]

Hi Wil Moosa:


A partial possible answer to your question is to relocate the cells with time (the cells that you don't want your users to see) to other remote locations on the worksheet....or..... to conceal them by making them invisible (they really are not)by formating the font color the same as the background color of the cell...ie, right click, format cells, font, white (if that what your background color is) Enter. Presto, they seem to disappear.

hope this helps

pll

 

[Dave Patton]

With 24:00 in A1, try looking at the various
formatting

- General shows 1 (1 day) etc.

 

[Wil Moosa]

...to conceal them by making them invisible (they really are not)by formating the font color the same as the background color of the cell...ie, right click, format cells, font, white...

very smart....

David, I figured it out. Got it working. Thanks to you all...