calculate time

miiia83

Board Regular
Joined
Feb 28, 2007
Messages
83
If you write 10.30-17.45 in a cell. is it possible to get it to calculate = 7,25h (in the next cell, not the same)?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
It would be easier if you wrote

10:30-17:45

with colons not stops

then, if that was in A1 this will give you 7,25

=MOD(RIGHT(A1,5)-LEFT(A1,5),1)*24

you need to ensure that all time show leading zeroes e.g

09:00-17:00

If you have to use stops not colons try

=MOD(RIGHT(SUBSTITUTE(A1,".",":"),5)-LEFT(SUBSTITUTE(A1,".",":"),5),1)*24
 

sosasola

New Member
Joined
Jan 26, 2007
Messages
29
It is possible, but only using macros. To give you an example I need to know a few more details:

calculating 10.30-17.45 gives a negative value. Do you wish to receive a module of a result?

how about time values like 9.30? will you write in the cell 9.30 or 09.30?
(It is easier to write a macro if you typed in 09.30).
 

miiia83

Board Regular
Joined
Feb 28, 2007
Messages
83
I can't get it to work, tried both ways. any idea what I might be doint wrong?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
The second formula should work if you have exactly 10.30-17.45 in A1 (with no additional spaces)

You need to format the cell with the formula as number. What result to you get, an error or the wrong value?
 

miiia83

Board Regular
Joined
Feb 28, 2007
Messages
83
it says: error in formula ?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Your version of Excel may require ; in formulas where mine uses , so try

=MOD(RIGHT(SUBSTITUTE(A1;".";":");5)-LEFT(SUBSTITUTE(A1;".";":");5);1)*24

similarly first formula would be

=MOD(RIGHT(A1;5)-LEFT(A1;5);1)*24
 

miiia83

Board Regular
Joined
Feb 28, 2007
Messages
83
Jeeej! that worked!! thanks a million!! thank god there are smart people to ask for help =)
 

Forum statistics

Threads
1,181,607
Messages
5,930,884
Members
436,764
Latest member
avalladarez

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