# calculate time

#### miiia83

##### Board Regular
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
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
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

##### New Member
barry houdini is right. Macro is not required.

#### miiia83

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

#### barry houdini

##### MrExcel MVP
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
it says: error in formula ?

#### barry houdini

##### MrExcel MVP
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
Jeeej! that worked!! thanks a million!! thank god there are smart people to ask for help =)

Replies
1
Views
619
Replies
1
Views
231
Replies
3
Views
196
Replies
18
Views
881
Replies
3
Views
210

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

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

### Which adblocker are you using?

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

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