I want a formula that will calculate time, this is my problem: Sheet 2,
A1=13:00, A2=12:00, A3=14:00. (these are the results from a time sheet
I created on Sheet 1), I need A4 to be the total of A1,2 & 3 Cells eg:
(39:00), Sounds easy but it just won't work, ive tried every format
there is and just about every formula. I know its using a 24 hour
timing but if A1 to A3 is the result of a calculation from Sheet 1 the
formula I put in A4 will change. Can anyone please help, Ive tried
about every site I know on the net and I can't find the answer.


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile: http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313

Format A4 as Custom, Type: [h]:mm
(include the 2 square brackets around the "h")
This allows roll-over 24 hours, and A4 will then show: 39:00
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chris Watson"
wrote in message
news:Chris.Watson.232ctn_1139658902.0502@excelforu m-nospam.com...

I want a formula that will calculate time, this is my problem: Sheet 2,
A1=13:00, A2=12:00, A3=14:00. (these are the results from a time sheet
I created on Sheet 1), I need A4 to be the total of A1,2 & 3 Cells eg:
(39:00), Sounds easy but it just won't work, ive tried every format
there is and just about every formula. I know its using a 24 hour
timing but if A1 to A3 is the result of a calculation from Sheet 1 the
formula I put in A4 will change. Can anyone please help, Ive tried
about every site I know on the net and I can't find the answer.


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile:
http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313

Just format Custom as [hh]:mm

=SUM(A1:A3)

--
Kind regards,

Niek Otten

"Chris Watson"
wrote in message
news:Chris.Watson.232ctn_1139658902.0502@excelforu m-nospam.com...

I want a formula that will calculate time, this is my problem: Sheet 2,
A1=13:00, A2=12:00, A3=14:00. (these are the results from a time sheet
I created on Sheet 1), I need A4 to be the total of A1,2 & 3 Cells eg:
(39:00), Sounds easy but it just won't work, ive tried every format
there is and just about every formula. I know its using a 24 hour
timing but if A1 to A3 is the result of a calculation from Sheet 1 the
formula I put in A4 will change. Can anyone please help, Ive tried
about every site I know on the net and I can't find the answer.


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile:
http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313

Try it for your self, Open a blank sheet, Format all cells as [hh]:mm,
in A1 put 06:00, in A2 put 05:00, in A3 put 07:00, in A4 put 08:00, now
B1 put in the formula =A1+A2 & in B2 put in formula =A3+A4. now put in
B3 =B1+B2. It just won't work which ever way you format the cells, I
have worked on this problem for 3 days now trying every possible
combination, I would give my right arm for the solution. I know this is
a 24 hour time calculation but if the cells you are adding together are
results of calculations from other cells the formula won't work. Try it
for your self. PLEASE PLEASE HELP!!!


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile: http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313

Of course we tried, we always do.
I retried your exact example from your last post and got 26:00, which is
correct.
You still didn't tell us what you get!

--
Kind regards,

Niek Otten


"Chris Watson"
wrote in message
news:Chris.Watson.232ily_1139666403.2172@excelforu m-nospam.com...

Try it for your self, Open a blank sheet, Format all cells as [hh]:mm,
in A1 put 06:00, in A2 put 05:00, in A3 put 07:00, in A4 put 08:00, now
B1 put in the formula =A1+A2 & in B2 put in formula =A3+A4. now put in
B3 =B1+B2. It just won't work which ever way you format the cells, I
have worked on this problem for 3 days now trying every possible
combination, I would give my right arm for the solution. I know this is
a 24 hour time calculation but if the cells you are adding together are
results of calculations from other cells the formula won't work. Try it
for your self. PLEASE PLEASE HELP!!!


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile:
http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313

I, too, followed your example, step-by-step and got a result of 26:00

The advice you've been given is correct

You must be doing something wrong


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=511313

hi

again you are telling your result!
what is your input??

then only ppl will diagonise the disease???!!!

-via135




Cutter Wrote:
I, too, followed your example, step-by-step and got a result of 26:00

The advice you've been given is correct

You must be doing something wrong


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=511313

OK this is the situation, I am designing a HGV Drivers timesheet which
includes deliveries and POA (Period of Availability) calculator. I have
10 sheets, sheet 1 is drivers details, Sheets 2 to 8 are days of the
week, sheet 9 is a weekly summary. as a test Sunday (Sheet 2) I started
work at 18:00 and finished at 07:00 giving 13:00 shift. Monday (sheet 3)
I started and finished the same as Sunday, I have transfered both total
times to sheet 9, 13:00 for sunday & 13:00 for monday. I have a
separate block of cells that calculate the weeks results so there is a
duty time cell with the total, adding both sunday & monday times
together gives me a total of 1130:00, not 26:00 that I want, I have
tried every format there is and can't get it to work. If anybody wants
me to email a copy of the time sheets to them I will with pleasure, I
have highlighted in orange the problem. There are no macros in my time
sheet.


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile: http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313

Please mail the workbook. It is just now that it occurs to me you include
dates.


--
Kind regards,

Niek Otten


"Chris Watson"
wrote in message
news:Chris.Watson.232rvb_1139678401.4576@excelforu m-nospam.com...

OK this is the situation, I am designing a HGV Drivers timesheet which
includes deliveries and POA (Period of Availability) calculator. I have
10 sheets, sheet 1 is drivers details, Sheets 2 to 8 are days of the
week, sheet 9 is a weekly summary. as a test Sunday (Sheet 2) I started
work at 18:00 and finished at 07:00 giving 13:00 shift. Monday (sheet 3)
I started and finished the same as Sunday, I have transfered both total
times to sheet 9, 13:00 for sunday & 13:00 for monday. I have a
separate block of cells that calculate the weeks results so there is a
duty time cell with the total, adding both sunday & monday times
together gives me a total of 1130:00, not 26:00 that I want, I have
tried every format there is and can't get it to work. If anybody wants
me to email a copy of the time sheets to them I will with pleasure, I
have highlighted in orange the problem. There are no macros in my time
sheet.


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile:
http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313

hi!

if then
Open a blank sheet, Format all cells as [hh]:mm, in A1 put 06:00, in A2
put 05:00, in A3 put 07:00, in A4 put 08:00, now B1 put in the formula
=A1+A2 & in B2 put in formula =A3+A4. now put in B3 =B1+B2.

you will get "26:00"

it's simple!

-via135





Chris Watson Wrote:
OK this is the situation, I am designing a HGV Drivers timesheet which
includes deliveries and POA (Period of Availability) calculator. I have
10 sheets, sheet 1 is drivers details, Sheets 2 to 8 are days of the
week, sheet 9 is a weekly summary. as a test Sunday (Sheet 2) I started
work at 18:00 and finished at 07:00 giving 13:00 shift. Monday (sheet 3)
I started and finished the same as Sunday, I have transfered both total
times to sheet 9, 13:00 for sunday & 13:00 for monday. I have a
separate block of cells that calculate the weeks results so there is a
duty time cell with the total, adding both sunday & monday times
together gives me a total of 1130:00, not 26:00 that I want, I have
tried every format there is and can't get it to work. If anybody wants
me to email a copy of the time sheets to them I will with pleasure, I
have highlighted in orange the problem. There are no macros in my time
sheet.


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=511313

Hi Niek Otten I need your email to send workbook?????


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile: http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313

Here is my workbook, Orange cells mark the



--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile: http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313

I have emailed Christ directly for his workbook. If I can help, I'll provide
the solution here. If I can't, I'll upload it and provide a link... :)
************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

"Chris Watson"
wrote in message
news:Chris.Watson.232v3y_1139682602.5497@excelforu m-nospam.com...

Here is my workbook, Orange cells mark the



--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile:
http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313

After tweaking, I've uploaded it he
http://www.officearticles.com/temp/chris.zip
Your first problem is that the cells in which you enter start and end time
have no dates. Since you're obviously spanning over midnight, you must tell
Excel the date as well or it believes the day is the same day.
Second problem is that all cells EXCEPT the time entry cells must be
formatted as [h]:mm because you want them to be ADDING hours, and not
calculating what the time would be after those hours are added.
:)
************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

"Anne Troy" wrote in message
...
I have emailed Christ directly for his workbook. If I can help, I'll
provide the solution here. If I can't, I'll upload it and provide a link...
:)
************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

"Chris Watson"
wrote in message
news:Chris.Watson.232v3y_1139682602.5497@excelforu m-nospam.com...

Here is my workbook, Orange cells mark the



--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile:
http://www.excelforum.com/member.php...fo&userid=4700
View this thread:
http://www.excelforum.com/showthread...hreadid=511313

Anne this is brilliant, your the headache tablet ive been looking for,
but I have a question ? is it possible to just type in the times in
Start and Finish without putting the date in, This timesheet is
designed for night shift work only so I will always start one day and
finish the next. Also there is one more problem thats bugging me and
that is if you look at cell U28, this is whats left of 48 hours POA in
a week, the law says that a driver of a HGV cannot exceed 48 hours POA
in a week and needs to know whats left. Cell L23 is the total POA of
the week so I need to take this figure away from 48:00, all I get is
#######.


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile: http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313

You're not really going to get away without putting in the date, and working
around that (having the date and time in 2 separate cells) will take someone
better than me. :)
It looks like POA will also work out if you format the cells as [h]:mm. And
you cannot have a "negative" time, really, so you need to handle that as
well.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

"Chris Watson"
wrote in message
news:Chris.Watson.2343jz_1139740202.9775@excelforu m-nospam.com...

Anne this is brilliant, your the headache tablet ive been looking for,
but I have a question ? is it possible to just type in the times in
Start and Finish without putting the date in, This timesheet is
designed for night shift work only so I will always start one day and
finish the next. Also there is one more problem thats bugging me and
that is if you look at cell U28, this is whats left of 48 hours POA in
a week, the law says that a driver of a HGV cannot exceed 48 hours POA
in a week and needs to know whats left. Cell L23 is the total POA of
the week so I need to take this figure away from 48:00, all I get is
#######.


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile:
http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313

You're not really going to get away without putting in the date,

If as Chris says the time are ALWAYS overnight then:

=MOD(Start Time - End time),1)

or

=Start Time - End time + (End Time < Start Time)

--
HTH

Sandy

with @tiscali.co.uk


"Anne Troy" wrote in message
...
You're not really going to get away without putting in the date, and
working around that (having the date and time in 2 separate cells) will
take someone better than me. :)
It looks like POA will also work out if you format the cells as [h]:mm.
And you cannot have a "negative" time, really, so you need to handle that
as well.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

"Chris Watson"
wrote in message
news:Chris.Watson.2343jz_1139740202.9775@excelforu m-nospam.com...

Anne this is brilliant, your the headache tablet ive been looking for,
but I have a question ? is it possible to just type in the times in
Start and Finish without putting the date in, This timesheet is
designed for night shift work only so I will always start one day and
finish the next. Also there is one more problem thats bugging me and
that is if you look at cell U28, this is whats left of 48 hours POA in
a week, the law says that a driver of a HGV cannot exceed 48 hours POA
in a week and needs to know whats left. Cell L23 is the total POA of
the week so I need to take this figure away from 48:00, all I get is
#######.


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile:
http://www.excelforum.com/member.php...fo&userid=4700
View this thread:
http://www.excelforum.com/showthread...hreadid=511313

I didn't say what I really meant:

If as Chris says the time are ALWAYS overnight then:

If the times are ALWAYS the same day or overnght, (ie never more than 24
hours between the Start/End times), then the formulas will return the
elapsed time without adding dates.


--
HTH

Sandy

with @tiscali.co.uk


"Sandy Mann" wrote in message
...
You're not really going to get away without putting in the date,

If as Chris says the time are ALWAYS overnight then:

=MOD(Start Time - End time),1)

or

=Start Time - End time + (End Time < Start Time)

--
HTH

Sandy

with @tiscali.co.uk


"Anne Troy" wrote in message
...
You're not really going to get away without putting in the date, and
working around that (having the date and time in 2 separate cells) will
take someone better than me. :)
It looks like POA will also work out if you format the cells as [h]:mm.
And you cannot have a "negative" time, really, so you need to handle that
as well.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com
Check out the NEWsgroup stats!
Check out: www.ExcelUserConference.com

"Chris Watson"
wrote in
message news:Chris.Watson.2343jz_1139740202.9775@excelforu m-nospam.com...

Anne this is brilliant, your the headache tablet ive been looking for,
but I have a question ? is it possible to just type in the times in
Start and Finish without putting the date in, This timesheet is
designed for night shift work only so I will always start one day and
finish the next. Also there is one more problem thats bugging me and
that is if you look at cell U28, this is whats left of 48 hours POA in
a week, the law says that a driver of a HGV cannot exceed 48 hours POA
in a week and needs to know whats left. Cell L23 is the total POA of
the week so I need to take this figure away from 48:00, all I get is
#######.


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile:
http://www.excelforum.com/member.php...fo&userid=4700
View this thread:
http://www.excelforum.com/showthread...hreadid=511313

Would it be possible to use AM & PM when entering times?????


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile: http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313

Would it be possible to use AM & PM when entering times?????

Yes, why don't you try it.

--
HTH

Sandy

with @tiscali.co.uk


"Chris Watson"
wrote in message
news:Chris.Watson.23574z_1139791502.8419@excelforu m-nospam.com...

Would it be possible to use AM & PM when entering times?????


--
Chris Watson
------------------------------------------------------------------------
Chris Watson's Profile:
http://www.excelforum.com/member.php...fo&userid=4700
View this thread: http://www.excelforum.com/showthread...hreadid=511313