Thread: using the MOD funktion in excel 2003

start time
11:00:00 PM (A3)
end time
3:00:00 AM (B3)


i am trying to use mod to work out the number of hours that a person
worked using excel

so i used the following formula =mod(B3-A3,1) i keep getting the
following error #Name?

thanks in advance for any help i can get


--
a2apple

=mod() shouldn't cause an error.

My first guess...

If you look at the column headers, do you see A, B, C, ...
or do you see 1, 2, 3, ...

If you see numbers, try changing this:
tools|Options|General Tab|uncheck R1C1 reference style

And reenter the formula.

===
Or maybe =mod() is called a different name. Do you use an English version of
excel?

If not, maybe this will help:

KeepItCool has a translator utility at:
http://members.chello.nl/keepitcool/download.html




a2apple wrote:
>
> start time
> 11:00:00 PM (A3)
> end time
> 3:00:00 AM (B3)
>
> i am trying to use mod to work out the number of hours that a person
> worked using excel
>
> so i used the following formula =mod(B3-A3,1) i keep getting the
> following error #Name?
>
> thanks in advance for any help i can get
>
> --
> a2apple

--

Dave Peterson

Maybe I'm missing something but why use Mod? Time is stored as decimal
values.
(3PM - 11AM) will be evaluated by Excel as (0.625 - 0.45833)

Mod returns the remainder of after division.
In this case the "remainder" after dividing by 1 (a day) gives you the same
results as simply doing the subtraction (which will still be a decimal
value)

If you want a "hours worked" result in whole hours, something like:
Int((B3-A3)*24)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

"a2apple" <a2apple.1wqutm@news.officefrustration.com> wrote in message
news:a2apple.1wqutm@news.officefrustration.com...
>
> start time
> 11:00:00 PM (A3)
> end time
> 3:00:00 AM (B3)
>
>
> i am trying to use mod to work out the number of hours that a person
> worked using excel
>
> so i used the following formula =mod(B3-A3,1) i keep getting the
> following error #Name?
>
> thanks in advance for any help i can get
>
>
> --
> a2apple

The reason is that if you use =MOD(end-start,1) you autmatically take next
day into account (if end time spans over midnight) where otherwise you would
need

=B3-A3+(B3<A3)

or you'll get a negative time value and formatting error if you use windows
date settings

Regards,

Peo Sjoblom


"George Nicholson" <JunkGeorgeN@msn.com> wrote in message
news:u82GHFpzFHA.3660@TK2MSFTNGP15.phx.gbl...
> Maybe I'm missing something but why use Mod? Time is stored as decimal
> values.
> (3PM - 11AM) will be evaluated by Excel as (0.625 - 0.45833)
>
> Mod returns the remainder of after division.
> In this case the "remainder" after dividing by 1 (a day) gives you the
same
> results as simply doing the subtraction (which will still be a decimal
> value)
>
> If you want a "hours worked" result in whole hours, something like:
> Int((B3-A3)*24)
>
> HTH,
> --
> George Nicholson
>
> Remove 'Junk' from return address.
>
> "a2apple" <a2apple.1wqutm@news.officefrustration.com> wrote in message
> news:a2apple.1wqutm@news.officefrustration.com...
> >
> > start time
> > 11:00:00 PM (A3)
> > end time
> > 3:00:00 AM (B3)
> >
> >
> > i am trying to use mod to work out the number of hours that a person
> > worked using excel
> >
> > so i used the following formula =mod(B3-A3,1) i keep getting the
> > following error #Name?
> >
> > thanks in advance for any help i can get
> >
> >
> > --
> > a2apple
>
>