In Excell how can i get a total hours worked value from 2 times?

[Ronnie Kray]

if i start work at 7am and leave work at 5pm
how do i calculate the time worked using a formular?

 

[T. Valko]

Try this:

A1 = 7:00 AM
B1 = 5:00 PM
D1 = formula:

=IF(COUNT(A1:B1)<2,"",MOD(B1-A1,1))

Format the formula cell as h:mm. Result = 10:00

If you want the result as a decimal then use this formula:

=IF(COUNT(A1:B1)<2,"",MOD(B1-A1,1)*24)

Format the formula cell as GENERAL or NUMBER. Result = 10

 

[RagDyeR]

To start, you must use XL recognizable times.

You'll need the colon ( : ) for hours and minutes;
7:30
15:45

For whole hours, XL helps with allowing you to use;
7<space>a
to get 7:00 AM
OR
11<space>p
to get 11:00 PM

With start time in A2:
7:00 AM
And end time in B2:
5:00 PM

Enter this formula in C2
=(B2-A2)*24
And format C2 to General or Number to display the hours worked.

If you times might span midnight, use this formula in C2:

=MOD(B2-A2,1)*24

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



if i start work at 7am and leave work at 5pm
how do i calculate the time worked using a formular?

 

[Teethless mama]

Why bother with MOD function. Keeep it simple

It should be suffice

=IF(COUNT(A1:B1)<2,"",B1-A1)

 

[RagDyeR]

Did you read my post?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Why bother with MOD function. Keeep it simple

It should be suffice

=IF(COUNT(A1:B1)<2,"",B1-A1)

 

[T. Valko]

Why bother with MOD function.

Since we don't know what kind of times will be used we can cover all hours
by using MOD or the other variation:

=IF(COUNT(A1:B1)<2,"",B1-A1+(B1>A1))

 

[T. Valko]

Typo in the formula:

=IF(COUNT(A1:B1)<2,"",B1-A1+(B1>A1))

Should be:

=IF(COUNT(A1:B1)<2,"",B1-A1+(B1<A1))

 

[Ronnie Kray]

Thanks Guys that works a treet but no i have a problem working out the Totla
Hour worked Per Week
mon
A1> 07:00 Stasrt
B1> 17:30 Finish
C1> 10:30 (hours worked With your previous formulars)

Tue
A2> 07:00 Start
B2> 16:45 Finish
C2> 9:45 Hours

So how do i add C1 & C2 to show total hours which should be
20 Hours 15 Mins

 

[T. Valko]

Try this:

=SUM(C1:C2)

Format as [h]:mm

 

[Ronnie Kray]

=SUM(C1:C2)
this works fine for 2 cells how do i add C1 C2 C3 C4 C5
if all daily hors are 10:00
i need it to show a total of 50:00 hours worked

ps sorry for the lame questions but its 18 years since i last worked on
excell at school
thx ronnie

Try this:

=SUM(C1:C2)

Format as [h]:mm

--
Biff
Microsoft Excel MVP

Thanks Guys that works a treet but no i have a problem working out the
Totla
Hour worked Per Week
mon
A1> 07:00 Stasrt
B1> 17:30 Finish
C1> 10:30 (hours worked With your previous formulars)

Tue
A2> 07:00 Start
B2> 16:45 Finish
C2> 9:45 Hours

So how do i add C1 & C2 to show total hours which should be
20 Hours 15 Mins

 

[T. Valko]

=SUM(C1:C2)

this works fine for 2 cells how do i add C1 C2 C3 C4 C5

=SUM(C1:C5)

--
Biff
Microsoft Excel MVP

=SUM(C1:C2)
this works fine for 2 cells how do i add C1 C2 C3 C4 C5
if all daily hors are 10:00
i need it to show a total of 50:00 hours worked

ps sorry for the lame questions but its 18 years since i last worked on
excell at school
thx ronnie

Try this:

=SUM(C1:C2)

Format as [h]:mm

--
Biff
Microsoft Excel MVP

Thanks Guys that works a treet but no i have a problem working out the
Totla
Hour worked Per Week
mon
A1> 07:00 Stasrt
B1> 17:30 Finish
C1> 10:30 (hours worked With your previous formulars)

Tue
A2> 07:00 Start
B2> 16:45 Finish
C2> 9:45 Hours

So how do i add C1 & C2 to show total hours which should be
20 Hours 15 Mins

 

[Ragdyer]

Under normal circumstances, the total time is subsequently multiplied by the
hourly rate to calculate the total monies due.

That is why, in my post, I suggested that you format the Column C cells to
Number or General.
That allows the totals to return a dollar amount when multiplied by the
rate.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

=SUM(C1:C2)
this works fine for 2 cells how do i add C1 C2 C3 C4 C5
if all daily hors are 10:00
i need it to show a total of 50:00 hours worked

ps sorry for the lame questions but its 18 years since i last worked on
excell at school
thx ronnie

Try this:

=SUM(C1:C2)

Format as [h]:mm

--
Biff
Microsoft Excel MVP

Thanks Guys that works a treet but no i have a problem working out the
Totla
Hour worked Per Week
mon
A1> 07:00 Stasrt
B1> 17:30 Finish
C1> 10:30 (hours worked With your previous formulars)

Tue
A2> 07:00 Start
B2> 16:45 Finish
C2> 9:45 Hours

So how do i add C1 & C2 to show total hours which should be
20 Hours 15 Mins