Stupid Time Question

[Sam Elowitch]

I'm just not getting anywhere and I need help with this:

I want to compute the number of hours between my daily start time and my
daily end time at work. Simple enough, right? But I can't figure out the
right formula. My questions are:

1. What format do the start and end times need to be in?
2. What's the formula? I tried =TIMEVALUE(C2-B2), but that doesn't work.

Help!

-Sam

 

[Domenic]

Hi Sam,

Some of the ways of entering times are as follows:

2 P
2 PM
2:00 PM
14:00

Ultimately, it doesn't matter how you choose to actually display them.
You can choose any format you wish.

To calculate the number of hours between two times,

assuming A1=start time and B1=end time,

=(B1-A1)*24

and format as general,

and if your times span over midnight, then you could try,

=((B1-A1)+(B1<A1))*24

Hope this helps!

 

[Sam Elowitch]

I get the darned #VALUE! message.

Shouldn't I enter this? =SUM(B1-A1)*24

-Sam

 

[Sam Elowitch]

Domenic,

It worked when I did this:

=SUM(B1-A1)*24 in the desired result field AND
entered my times as "10:00 p" and "9:00 a"

My problem apparently was that Excel wasn¹t recognizing my data as times.

-Sam

 

[Domenic]

Glad you've sorted it out!

Cheers!

 

[JE McGimpsey]

I get the darned #VALUE! message.

Shouldn't I enter this? =SUM(B1-A1)*24

No.

SUM() in this case is redundant - XL will first evaluate B1-A1, then
pass that result to SUM(). Since there's no other argument, SUM() just
sums that value, which will, of course, return the same result.

The #VALUE! error is almost certainly because one or both of your times
are actually Text. This often happens when you paste the time in from a
different source, or if you have a cell preformatted as Text when you
enter the value.

Try reformatting the cells as General or a time, and then reentering the
times.

Domenic made the excellent suggestion that you use

=(B1-A1+(B1<A1))*24

to avoid an error if your times span midnight. A slightly more efficient
and obscure way is

=MOD(B1-A1,1)*24

 

[Domenic]

to avoid an error if your times span midnight. A slightly more efficient
and obscure way is

=MOD(B1-A1,1)*24

Hmmm...interesting!

 

[Rob]

I'm just not getting anywhere and I need help with this:

I want to compute the number of hours between my daily start time and my
daily end time at work. Simple enough, right? But I can't figure out the
right formula. My questions are:

1. What format do the start and end times need to be in?
2. What's the formula? I tried =TIMEVALUE(C2-B2), but that doesn't work.

Help!

-Sam

Use a 24 hour clock (2 PM = 14:00) and add the date as well:

You start your shift today at 22-06-2004 20:00 [dd-mm-yyyy hh:mm] in cell A1
You end your shift tomorrow at 23-06-2004 04:00 [dd-mm-yyyy hh:mm] in
cell A2

formula in cell A3:

=A2-A1

Select cell A3, point to Format > Cells > Number tab > Custom
Change the formatting to [hh]:mm (yes - square brackets)

You have worked 08:00

If your shift is not overnight just enter the times in hh:mm format:

Start at 09:00 (in cell A1)
Leave at 17:00 (in cell A2)

In the office: =A2-A1

Result of formula: 08:00 hours of boring labour :-(

PS in Europe we don't use AM and PM so I never tried formatting like 2PM
or so. Entering dates in a mm-dd-yyyy should work the same as my
example. Notice the single space between the date and time.

Hope this also helps - Rob