# Calculating Hours Worked

#### dhayes1963

##### New Member
If I format A1 and B1 for time, and in A1 enter 9:00 am or 09:00, and in B1 enter 5:00 pm or 17:00, how do I make C1 = B1 - A1 such that the result in C1 is a number of hours worked (i.e. 8.00) ? Nothing I seem to do works. Thanks in advance. Drew.

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### sunnyland

##### Well-known Member
Hello,

Try:
Book1
ABCD
1StartFinishHoursformattedasnumbernothours
29:005:0020.00
Sheet1

formula being:
Code:
``=((((B2<A2)*1)+B2)-A2)*24``
meaning if b2<a2 then we add 1 to the earliest time:
ie start 9:00 finish 4:00

#### barry houdini

##### MrExcel MVP
If I format A1 and B1 for time, and in A1 enter 9:00 am or 09:00, and in B1 enter 5:00 pm or 17:00, how do I make C1 = B1 - A1 such that the result in C1 is a number of hours worked (i.e. 8.00) ? Nothing I seem to do works. Thanks in advance. Drew.

If you want the result in decimal format then one way is

=MOD(B1-A1,1)*24

#### dhayes1963

##### New Member
Thank you gentlemen.

#### sunnyland

##### Well-known Member
Hello to you both,

By the way I am a female.

Interesting suggestion Barry as it did open some questionning for me.

I never thought of mod as I am more familiar with VBA and I only took recently to formula. In VBA mod wouldn't return this value.

Code:
``````Sub differencewithmodVBAandModinFormula()
'using value 9:00 to 15:00
'using 0.375 and 0.625 respectively
'using 0.625-0375 = 0.25
MsgBox "VBA 0.25 MOD 1=" & 0.25 Mod 1
MsgBox "Corresponding  0.25 - ((Int(0.25 / 1)) * 1)= " & 0.25 - ((Int(0.25 / 1)) * 1)
' it seems that the vba version of mod is less accurate and round up or down before to calculate
'never thought of that before
'using 16.55 mod 3 will result in  in VBA:      2
'using 16.55 mod 3 will result in  EXCEL :   1.55
'using 16.45 mod 3 will result in  VBA:         1
'using 16.45 mod 3 will result in EXCEL:     1.45

End Sub``````

A shame the Excel mod(a,b) doesn't seem to be available in VBA.
Interesting how sometimes resolving one problem may uncover another
Have a nice weekend

#### dhayes1963

##### New Member
Francoise, My profound apologies for referring to you as a "gentleman". Many thanks for your assistance. Drew (St Kilda)

#### mortgageman

##### Well-known Member
Francoise:

I'm pretty sure that mod returns the same value in vba and in excel as long as both arguments are integers. mod really isn't defined well (or at all IMHO) for fractions. IMHO excel or vba should not accept arguments for mod unless they are integers.

Gene, "The Mortgage Man", Klein

#### sunnyland

##### Well-known Member
Hello to you all,

No need to apologise Drew, I just wanted to underline that there is a few women out there that enjoy racking their brains trying to solve problems and I just happen to be one of them.

I left Melbourne, 2 years ago for the sunny Queensland (Gold Coast). No regrets so far.

Gene,

From my trial:
mod(125,7)
Mod formula in Excel accept decimal in both parameters and doesn't round up.

if 1st parameter is smaller then 2nd parameter, whatever the second is, it will return the first one without rounding if decimal.Ie: mod(3.29,6.45) =3.29
The result of mod in excel can be decimal.

mod in VBA125 mod 7
VBA seems to returns the first parameter if first parameter smaller then second. The problem is the rounding in VBA:
It seems that vba mod round up if decimal used, everything over the.5.

6 mod 6.49 will result in 0 (6.49 is rounded down)
6 mod 6.51 will result in 6 (6.51 is rounded up to 7 so first parameter returned)

now 6.41 mod 9.51 will result in 6 (1st parameter rounded down)
now 6.51 mod 9.51 will result in 7 (1st parameter rounded up)

Interesting there is no constitency between a formula in Excel and equivalent in VBA and can result in weird answers.

#### mortgageman

##### Well-known Member
Fancoise:

1) In BOTH VBA and excel, 125 mod 7 returns 6. This is of course correct, as when you divide 7 into 125 the remainder is 6.

2) The fact that mod behaves inconsistently between VBA and Excel for decimal arguments is to me futher evidence that you should just not use mod for decimals. Using functional language, Mod is not defined for non integer domain. What is the "meaning" of mod(b,a) when b and a are decimals? What is the meaning when b and a are irrationals (non terminating, non repeating?). I agree that inconsistent behavior is not to be desired. The correct behavior should be #NUM# or #VALUE#. (Actually - #VALUE for sure)

Gene, "The Mortgage Man", Klein

Replies
18
Views
685
Replies
2
Views
122
Replies
3
Views
420
Replies
1
Views
403
Replies
5
Views
388

1,171,179
Messages
5,874,192
Members
433,033
Latest member
thatmacroguy

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