Time 0:00

Wil Moosa

Well-known Member
Joined
Aug 11, 2002
Messages
882
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?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
=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

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Aug 11, 2002
Messages
882
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
With 24:00 in A1, try looking at the various
formatting

- General shows 1 (1 day) etc.
 

Wil Moosa

Well-known Member
Joined
Aug 11, 2002
Messages
882
...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...
 

Forum statistics

Threads
1,144,274
Messages
5,723,447
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top