Calculating Hours Worked

dhayes1963

New Member
Joined
Oct 31, 2002
Messages
15
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
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
Joined
Mar 23, 2005
Messages
20,825
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
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912

ADVERTISEMENT

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
Joined
Oct 31, 2002
Messages
15
Francoise, My profound apologies for referring to you as a "gentleman". Many thanks for your assistance. Drew (St Kilda)
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015

ADVERTISEMENT

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
Joined
Jan 27, 2006
Messages
912
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
Joined
Jun 30, 2005
Messages
2,015
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
 

Forum statistics

Threads
1,136,431
Messages
5,675,806
Members
419,586
Latest member
RoteichA

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