calculate time

[miiia83]

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)?

 

[barry houdini]

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]

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).

 

[sosasola]

barry houdini is right. Macro is not required.

 

[miiia83]

I can't get it to work, tried both ways. any idea what I might be doint wrong?

 

[barry houdini]

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]

it says: error in formula ?

 

[barry houdini]

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]

Jeeej! that worked!! thanks a million!! thank god there are smart people to ask for help =)