Excel Calculating Time

N

ng66

Can Excel calculate daily hours with just a cell that contains a start time
and one that contains an end time? I tried different things but they all came
out with ###VALUE or ###REF and junk like that...

THANKS YET AGAIN!!
 
D

David Biddulph

08:30 in A1
17:15 in B1
=B1-A1 in C1 gives 08:45
If the times might go over midnight, use =B1-A1+(A1>B1)
 
S

Sandy Mann

With times entered as XL time (ie like 8:45 with a full colon)

Start time in B2
End time in C2

Hours in D2:

=C2-B2
and format the cell as custom hh:mm

If you SUM() the hours worked the custom format the cell as [hh]:mm
which will stop the hours rolling over into days when they reach 24

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
N

ng66

So it has to be done in military time, ie 20:15??

Sandy Mann said:
With times entered as XL time (ie like 8:45 with a full colon)

Start time in B2
End time in C2

Hours in D2:

=C2-B2
and format the cell as custom hh:mm

If you SUM() the hours worked the custom format the cell as [hh]:mm
which will stop the hours rolling over into days when they reach 24

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk


ng66 said:
Can Excel calculate daily hours with just a cell that contains a start
time
and one that contains an end time? I tried different things but they all
came
out with ###VALUE or ###REF and junk like that...

THANKS YET AGAIN!!
Click to expand...
Click to expand...
 
D

David Biddulph

8:15 PM will work equally well, if you prefer.
--
David Biddulph

ng66 said:
So it has to be done in military time, ie 20:15??

Sandy Mann said:
With times entered as XL time (ie like 8:45 with a full colon)

Start time in B2
End time in C2

Hours in D2:

=C2-B2
and format the cell as custom hh:mm

If you SUM() the hours worked the custom format the cell as [hh]:mm
which will stop the hours rolling over into days when they reach 24

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk


ng66 said:
Can Excel calculate daily hours with just a cell that contains a start
time
and one that contains an end time? I tried different things but they
all
came
out with ###VALUE or ###REF and junk like that...

THANKS YET AGAIN!!
Click to expand...
Click to expand...
Click to expand...
 
N

ng66

Perfect, thanks!

David Biddulph said:
8:15 PM will work equally well, if you prefer.
--
David Biddulph

ng66 said:
So it has to be done in military time, ie 20:15??

Sandy Mann said:
With times entered as XL time (ie like 8:45 with a full colon)

Start time in B2
End time in C2

Hours in D2:

=C2-B2
and format the cell as custom hh:mm

If you SUM() the hours worked the custom format the cell as [hh]:mm
which will stop the hours rolling over into days when they reach 24

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk


Can Excel calculate daily hours with just a cell that contains a start
time
and one that contains an end time? I tried different things but they
all
came
out with ###VALUE or ###REF and junk like that...

THANKS YET AGAIN!!
Click to expand...
Click to expand...
Click to expand...
 
S

Sandy Mann

I always prefered to have people entering time in 24 hour format because it
is too easy for inexperienced operators to enter 8:15PM instead of 8:15 PM
and end up with #VALUE! errors. In fact the resulting time *has* to be
formatted as hh:mm otherwise you will get an elapsed time of, say, 2:15 AM.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk


ng66 said:
Perfect, thanks!

David Biddulph said:
8:15 PM will work equally well, if you prefer.
--
David Biddulph

ng66 said:
So it has to be done in military time, ie 20:15??

:

With times entered as XL time (ie like 8:45 with a full colon)

Start time in B2
End time in C2

Hours in D2:

=C2-B2
and format the cell as custom hh:mm

If you SUM() the hours worked the custom format the cell as [hh]:mm
which will stop the hours rolling over into days when they reach 24

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk


Can Excel calculate daily hours with just a cell that contains a
start
time
and one that contains an end time? I tried different things but they
all
came
out with ###VALUE or ###REF and junk like that...

THANKS YET AGAIN!!
Click to expand...
Click to expand...
Click to expand...
 
Top