date and time difference

[Stumped]

A | B | C | D |
E
1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM |
2|
3|
4|
5|

Log for short and long term visits. Colums A&B being start date and time in
that format Colums C&D being end date and time.
Need E1 to = total duration in Format YY/MM/DD HH:MM
And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM
Please help, been at this problem for weeks now.

 

[Stumped]

E got cut off should be at the end.

 

[Fred Smith]

e1: =c1+d1-a1-b1
Format as desired
e5: = sum(e1:e4)
Format as desired

Regards,
Fred

 

[Stumped]

tried what you suggested and got #value error. have tried several versions of
what u sent with no success. I really am stumped

 

[Fred Smith]

Then you must have text (rather than a number) in one or more of your cells.

Do this:
-- Enter 1 in an empty cell
-- Copy that cell
-- Highlight cells a1 through e4
-- Right click, choose Paste Special...> Multiply.

Do the results come through now?

Regards,
Fred

 

[CellShocked]

A | B | C | D |
E
1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM |
2|
3|
4|
5|

Log for short and long term visits. Colums A&B being start date and time in
that format Colums C&D being end date and time.
Need E1 to = total duration in Format YY/MM/DD HH:MM
And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM
Please help, been at this problem for weeks now.

Look at the formulas in these time cells...


http://office.microsoft.com/en-us/templates/TC300083091033.aspx?pid=CT101172771033

 

[Stumped]

sorry bout the late response, did not git notice of new posts

Cellshocked, I did nort see any formulas is that spreed sheet that would
give me the results that I am lookinf for.

Fred, I double checked my formating, no text. A1 and C1 = date format, B1
and D1 = time format, E1 = custom format yy:mm:dd hh:mm

Thank u both for the time but I still have not found a solution. Do you even
think it is possible to get the results I am looking for?

 

[Fred Smith]

Absolutely you can get the results you are looking for. This should be an
easy two minute solution.

I still believe the problem is that your cells are text. Unfortunately,
checking the formatting doesn't always do the job. Do the following:
-- try to change the formatting. Does the display change? If not, then you
have text.
-- check for text by using =istext(a1) in another cell. What's the result?
-- do this for all of your cells

If you find a cell with text in it, then change it to a number. Then the
formula should work.

Regards,
Fred

 

[CellShocked]

See my interspersed response below...

Absolutely you can get the results you are looking for. This should be an
easy two minute solution.

Absolutely it is obvious that you did not even examine the problem(s)
involved.

I still believe the problem is that your cells are text. Unfortunately,
checking the formatting doesn't always do the job. Do the following:
-- try to change the formatting. Does the display change? If not, then you
have text.

Did you bother to notice that his start time is divided into two cells,
as is his stop time?

Provide a formula that concatenates (not with the excel function) those
two together into a proper date/time string. That has to happen first.

The two cells may have to be concatenated in another cell, and then
acted on by a formula to be seen as a proper timestamp, and get correct
math out of it.

-- check for text by using =istext(a1) in another cell. What's the result?
-- do this for all of your cells

You are barking up the wrong tree. The way it looks, his original
cells NEED to be converted to a single date and time cell value FIRST He
has more than one format just in the single line of data he gave.

If you find a cell with text in it, then change it to a number. Then the
formula should work.

Your three second glance at this post/thread is an insult.

 

[CellShocked]

Try this in E1

=(DATEVALUE(C1)+TIMEVALUE(D1))-(DATEVALUE(A1)+TIMEVALUE(B1))

That works for text fields.

This one works if they are all indeed date and time formatted.

=(C1+D1)-(A1+B1)

You also need to change your cell formatting to "[hh]" instead of
simply "hh" because you are adding more than 24 hour periods. As in:

yy:mm:dd [hh]:mm

 

[Fred Smith]

My posted solution was:
e1: =c1+d1-a1-b1

Isn't that the same as yours?

Fred

 

[CellShocked]

My posted solution was:
e1: =c1+d1-a1-b1

Isn't that the same as yours?

That yields an incorrect answer. They have to be encapsulated.

Like I said, you did not even try the 4 cell problem, apparently.

You were on about his fields being formatted text, even after he stated
that they were not.

I solved it even for text formatting, but I do not know if I included
that text in my reply.

No biggie.

 

[Fred Smith]

Your answer was: =(C1+D1)-(A1+B1)
My answer was: =c1+d1-a1-b1

These are mathematically identical. They do not "have to be encapsulated".
My formula yields exactly the same result as yours.

Regards,
Fred

 

[Stumped]

OK guys settle down.
=(C1+D1)-(A1+B1) works with 2 exceptions, No matter the dates entered (less
than 1 month difference) it will show that one month has passed when in fact
it may only be a day or two. Also CellShocked, when I place the brackets
around hh it allways shows 12 in the hours position in E1. when I leave them
off it appears to count correctly. Shold I leave them on or off??

I also discovered an error on my part. Even though the date cells were
formated to just date, when I looked in the formula bar I could see that
excell had entered a random time as well. I could enter the same date into
the same cell and each time it would assign a apparently random time to it as
well. not sure what that was about but got that fixed. So u both may have
been right.

But the above exceptions still exist.
P.S. I am not recieving notices about updates to this post even though the
box is checked. oh well another post for that.

 

[Stumped]

Update:
after testing various times and dates the hours seem to be counting
correctly (adding 1 day for every 24) even in the E5 cell without the " [ ]
"so I am leaving them out of the formating.
Everthing seems to be ok except the additional 1 month when there has not
been 1 month pass. In the E5 cell the "false" 1 month is not added to the sum
but is still shown. let me know if I confuseing you. Thank u both for the
time and effort.

 

[Fred Smith]

When you display yy/mm/dd (with or without the time), months start at 1.
There is no month zero. This is just the way months are displayed. You
cannot use a date format if you want to have a month zero.

If you want to get around this, give us sample results, and what you would
like displayed. In particular, how many days do you expect your "months" to
have.

Regards,
Fred

Update:
after testing various times and dates the hours seem to be counting
correctly (adding 1 day for every 24) even in the E5 cell without the "
[ ]
"so I am leaving them out of the formating.
Everthing seems to be ok except the additional 1 month when there has not
been 1 month pass. In the E5 cell the "false" 1 month is not added to the
sum
but is still shown. let me know if I confuseing you. Thank u both for the
time and effort.

A | B | C | D
|
E
1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM |
2|
3|
4|
5|

Log for short and long term visits. Colums A&B being start date and time
in
that format Colums C&D being end date and time.
Need E1 to = total duration in Format YY/MM/DD HH:MM
And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM
Please help, been at this problem for weeks now.

 

[Stumped]

Maybe if I explain what I am trying to do it will help you.
My job includes a lot of travel and I track the time at different locations
in lump sums from arival on site to departure from site. The entervals
usaually range 1 week or less but on ocasionaly over a month and even just
over 1 year. the input data is excactly as in the origional post and formated
as shown (date and time) I am looking for the E colum to compute total
difference. yy/mm/dd hh:mm. been doing it for years on paper, that gets old!!
everything apears to work as I would like except that 1 month thing. Months
need to reset after 12 and add 1 to the years.

Thank u for all the help

When you display yy/mm/dd (with or without the time), months start at 1.
There is no month zero. This is just the way months are displayed. You
cannot use a date format if you want to have a month zero.

If you want to get around this, give us sample results, and what you would
like displayed. In particular, how many days do you expect your "months" to
have.

Regards,
Fred

Update:
after testing various times and dates the hours seem to be counting
correctly (adding 1 day for every 24) even in the E5 cell without the "
[ ]
"so I am leaving them out of the formating.
Everthing seems to be ok except the additional 1 month when there has not
been 1 month pass. In the E5 cell the "false" 1 month is not added to the
sum
but is still shown. let me know if I confuseing you. Thank u both for the
time and effort.

A | B | C | D
|
E
1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM |
2|
3|
4|
5|

Log for short and long term visits. Colums A&B being start date and time
in
that format Colums C&D being end date and time.
Need E1 to = total duration in Format YY/MM/DD HH:MM
And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM
Please help, been at this problem for weeks now.

.

 

[Fred Smith]

Give us examples of what you do on paper, then we can tell you how to do it
in Excel. A good example for you to define would be Jan 31 to Mar 1. How
many months and days is this?

Regards,
Fred

Maybe if I explain what I am trying to do it will help you.
My job includes a lot of travel and I track the time at different
locations
in lump sums from arival on site to departure from site. The entervals
usaually range 1 week or less but on ocasionaly over a month and even just
over 1 year. the input data is excactly as in the origional post and
formated
as shown (date and time) I am looking for the E colum to compute total
difference. yy/mm/dd hh:mm. been doing it for years on paper, that gets
old!!
everything apears to work as I would like except that 1 month thing.
Months
need to reset after 12 and add 1 to the years.

Thank u for all the help

When you display yy/mm/dd (with or without the time), months start at 1.
There is no month zero. This is just the way months are displayed. You
cannot use a date format if you want to have a month zero.

If you want to get around this, give us sample results, and what you
would
like displayed. In particular, how many days do you expect your "months"
to
have.

Regards,
Fred

Update:
after testing various times and dates the hours seem to be counting
correctly (adding 1 day for every 24) even in the E5 cell without the "
[ ]
"so I am leaving them out of the formating.
Everthing seems to be ok except the additional 1 month when there has
not
been 1 month pass. In the E5 cell the "false" 1 month is not added to
the
sum
but is still shown. let me know if I confuseing you. Thank u both for
the
time and effort.

:

A | B | C | D
|
E
1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM |
2|
3|
4|
5|

Log for short and long term visits. Colums A&B being start date and
time
in
that format Colums C&D being end date and time.
Need E1 to = total duration in Format YY/MM/DD HH:MM
And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM
Please help, been at this problem for weeks now.

.

 

[Stumped]

Ok here goes:
02/07/08 10:30 AM -- 03/18/09 3:00 PM = 01/01/08 04:30
05/22/09 12:00 PM -- 05/26/09 7:00 PM = 00/00/04 07:00
06/08/09 01:00 PM -- 07/16/09 3:00 PM = 00/01/08 02:00
07/18/09 10:30 AM -- 09/28/09 3:00 PM = 00/02/12 04:30

----------------------
total = 01/04/31 18:00
everything in E colum is yy/mm/dd hh:mm custom format.
Days roll over to months after 28-31 days depending on month.
months roll over to years after 12
and total E5 = sum of all differences

Give us examples of what you do on paper, then we can tell you how to do it
in Excel. A good example for you to define would be Jan 31 to Mar 1. How
many months and days is this?

Regards,
Fred

Maybe if I explain what I am trying to do it will help you.
My job includes a lot of travel and I track the time at different
locations
in lump sums from arival on site to departure from site. The entervals
usaually range 1 week or less but on ocasionaly over a month and even just
over 1 year. the input data is excactly as in the origional post and
formated
as shown (date and time) I am looking for the E colum to compute total
difference. yy/mm/dd hh:mm. been doing it for years on paper, that gets
old!!
everything apears to work as I would like except that 1 month thing.
Months
need to reset after 12 and add 1 to the years.

Thank u for all the help

When you display yy/mm/dd (with or without the time), months start at 1.
There is no month zero. This is just the way months are displayed. You
cannot use a date format if you want to have a month zero.

If you want to get around this, give us sample results, and what you
would
like displayed. In particular, how many days do you expect your "months"
to
have.

Regards,
Fred

Update:
after testing various times and dates the hours seem to be counting
correctly (adding 1 day for every 24) even in the E5 cell without the "
[ ]
"so I am leaving them out of the formating.
Everthing seems to be ok except the additional 1 month when there has
not
been 1 month pass. In the E5 cell the "false" 1 month is not added to
the
sum
but is still shown. let me know if I confuseing you. Thank u both for
the
time and effort.

:

A | B | C | D
|
E
1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM |
2|
3|
4|
5|

Log for short and long term visits. Colums A&B being start date and
time
in
that format Colums C&D being end date and time.
Need E1 to = total duration in Format YY/MM/DD HH:MM
And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM
Please help, been at this problem for weeks now.

.

.

 

[Stumped]

Fred, Cellshocked u guys still out there? At least let me tell u thanks for
all the help and time u guys put in... Thanks

Ok here goes:
02/07/08 10:30 AM -- 03/18/09 3:00 PM = 01/01/08 04:30
05/22/09 12:00 PM -- 05/26/09 7:00 PM = 00/00/04 07:00
06/08/09 01:00 PM -- 07/16/09 3:00 PM = 00/01/08 02:00
07/18/09 10:30 AM -- 09/28/09 3:00 PM = 00/02/12 04:30

----------------------
total = 01/04/31 18:00
everything in E colum is yy/mm/dd hh:mm custom format.
Days roll over to months after 28-31 days depending on month.
months roll over to years after 12
and total E5 = sum of all differences

Give us examples of what you do on paper, then we can tell you how to do it
in Excel. A good example for you to define would be Jan 31 to Mar 1. How
many months and days is this?

Regards,
Fred

Maybe if I explain what I am trying to do it will help you.
My job includes a lot of travel and I track the time at different
locations
in lump sums from arival on site to departure from site. The entervals
usaually range 1 week or less but on ocasionaly over a month and even just
over 1 year. the input data is excactly as in the origional post and
formated
as shown (date and time) I am looking for the E colum to compute total
difference. yy/mm/dd hh:mm. been doing it for years on paper, that gets
old!!
everything apears to work as I would like except that 1 month thing.
Months
need to reset after 12 and add 1 to the years.

Thank u for all the help

:

When you display yy/mm/dd (with or without the time), months start at 1.
There is no month zero. This is just the way months are displayed. You
cannot use a date format if you want to have a month zero.

If you want to get around this, give us sample results, and what you
would
like displayed. In particular, how many days do you expect your "months"
to
have.

Regards,
Fred

Update:
after testing various times and dates the hours seem to be counting
correctly (adding 1 day for every 24) even in the E5 cell without the "
[ ]
"so I am leaving them out of the formating.
Everthing seems to be ok except the additional 1 month when there has
not
been 1 month pass. In the E5 cell the "false" 1 month is not added to
the
sum
but is still shown. let me know if I confuseing you. Thank u both for
the
time and effort.

:

A | B | C | D
|
E
1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM |
2|
3|
4|
5|

Log for short and long term visits. Colums A&B being start date and
time
in
that format Colums C&D being end date and time.
Need E1 to = total duration in Format YY/MM/DD HH:MM
And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM
Please help, been at this problem for weeks now.

.

.