Formula is driving me crazy?????

C

Chris Watson

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.
 
M

Max

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
 
C

Chris Watson

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!!!
 
N

Niek Otten

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" <[email protected]>
wrote in message
news:[email protected]...
 
C

Cutter

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
 
V

via135

hi

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

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

-via135
 
C

Chris Watson

OK this is the situation, I am designing a HGV Drivers timesheet whic
includes deliveries and POA (Period of Availability) calculator. I hav
10 sheets, sheet 1 is drivers details, Sheets 2 to 8 are days of th
week, sheet 9 is a weekly summary. as a test Sunday (Sheet 2) I starte
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 tota
times to sheet 9, 13:00 for sunday & 13:00 for monday. I have
separate block of cells that calculate the weeks results so there is
duty time cell with the total, adding both sunday & monday time
together gives me a total of 1130:00, not 26:00 that I want, I hav
tried every format there is and can't get it to work. If anybody want
me to email a copy of the time sheets to them I will with pleasure,
have highlighted in orange the problem. There are no macros in my tim
sheet
 
V

via135

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
 
A

Anne Troy

After tweaking, I've uploaded it here:
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
 
C

Chris Watson

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
#######.
 
A

Anne Troy

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" <[email protected]>
wrote in message
news:[email protected]...
 
S

Sandy Mann

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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


Anne Troy said:
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" <[email protected]>
wrote in message
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?action=getinfo&userid=4700
View this thread:
http://www.excelforum.com/showthread.php?threadid=511313
Click to expand...
Click to expand...
 
S

Sandy Mann

I didn't say what I really meant:
If as Chris says the time are ALWAYS overnight then:
Click to expand...

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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


Sandy Mann said:
You're not really going to get away without putting in the date,
Click to expand...

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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


Anne Troy said:
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"
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?action=getinfo&userid=4700
View this thread:
http://www.excelforum.com/showthread.php?threadid=511313
Click to expand...
Click to expand...
Click to expand...
 
S

Sandy Mann

Would it be possible to use AM & PM when entering times?????Yes, why don't you try it.

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


"Chris Watson" <[email protected]>
wrote in message
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please Please Help!!!! 4
Time Formula????? 2
WORK HOURS DIFFERENCE BETWEEN TWO DATES 12
Work Hours 1
Help Creating Excel Formula 4
Time Span calculations 4
Calculate elapsed time excluding specific timeframe 10
formula question 6

Top