Combining date and time in order to get the difference

[rantz]

I asked a similar question a couple a days ago, maybe it's clearer
this time.

I have a problem regarding date and time in excel. Perhaps I should
explain that I'm working with dates and time in Swedish format. It
should not matter thou.

Each row is an item in a warehouse. I need to get the difference
between in and out. It is not a problem how to get the difference in
dates, should just be '=C2-A2', same thing with the time.

The thing is, I would like to get the difference regarding both date
and time in one cell in order to be able to use that value in a
pivot-table.

I don't even know if this is possible. As you can see, there is a
problem with every time it crosses the dateline (see row 4 and 5).

I thought about turning column A and B into one cell formatted like
'yy:mm:dd, hh;mm:ss' and same thing with C and D. Then I thought I
could get the difference by subtracting the two new cells and get
something like '44 days, 3:22'. I could settle for something like
hh:mm:ss (86:25:24) too.

The problem is that I don't know how to format the cells and I
don't know how excel interprets the information.

I've been looking at http://www.cpearson.com/excel/datetime.htm but I
can't figure it out.

Perhaps I could use function EXTEXT to isolate the different elements
and use YEAR(), WEEK() etc..

I hope someone can help me.


A B C D
1 INDATE INTIME OUTDATE OUTTIME
2 04-10-01 23:37:33 04-10-13 14:54:39
3 04-10-01 23:38:04 04-10-13 14:55:30
4 04-10-01 23:39:36 04-10-13 14:45:17
5 04-10-02 00:08:25 04-10-13 19:23:44
6 04-10-02 00:28:48 04-10-13 14:48:49
7 04-10-02 00:28:49 04-10-13 14:49:56

 

[Richard.Toren]

Subject: Re: Combining date and time in order to get the difference

What you'd like to do is something like this:
=( datevalue( c1) + timevalue(d1) ) - ( datevalue(a1) + timevalue(b1) )

but you are getting thrown off by what is a valid datevalue. I had the same problem with my european date format (standard in Israel). I got around it by extracting the year, month and day values from the column as a string and using the date( year, month, day) function instead. You'll have to change 01 to 2001 etc...



-----Original Message-----

 

[rantz]

The " =( datevalue( c1) + timevalue(d1) ) - ( datevalue(a1) +
timevalue(b1) ) " seams like a really good idea. Thank you!

How is the datevalue not valid? Of course I can change 04 to 2004, but
I don't think it will be any more valid.

What do you mean by "extracting the year, month and day values" do you
mean something like this? ( C5='2004-10-13')

=DATE(EXTEXT(C5;1;4);EXTEXT(C5;6;2);EXTEXT(C5;9;2))

All I get from this formula is #ERROR, but perhaps this is beacuse of
the invalid datevalue. How do I get valid datevalue?

 

[Richard.Toren]

Subject: Re: Combining date and time in order to get the difference

The year is =left(a1,2) + 2000
The month is =mid(a1,4,2)
The day is =right(a1,2)

So = date( left(a1,2) + 2000, mid(a1,4,2), right(a1,2) )

gives you the calendar date.

= date( left(a1,2) + 2000, mid(a1,4,2), right(a1,2) ) + timevalue( b2)
gives you the date to the minute


The whole thing will look terrible, but I think you have the idea.

-----Original Message-----

 

[Pete]

Excel stores dates internally as the number of elapsed days from some
reference date. Times are stored as fractions of a 24-hour day. So you
should be able to use the above formula:

=( datevalue( c1) + timevalue(d1) ) - ( datevalue(a1) + timevalue(b1)
),

although I've found that you can add or subtract the cells directly,
i.e.

= C1 + D1 - A1 - B1

You can format the cell with this formula in various ways, e.g.:

[m]:ss would give you total minutes and seconds, eg 69:30, or 1537:23
[h]:mm:ss would give total hours plus minutes and seconds, eg 25:15:46

Pete (UK)

 

[rantz]

Ok, I think I've figured out what the problem is. I don't know how to
fix it thou.

A11='2004-10-02' it is formatted as YYYY-MM-DD

When I use the formula '=LEFT(A11,4)' I get '3826'
When I use the formula '=MID(A11,6,2)' I get ''
When I use the formula '=RIGHT(A11,2)' I get '62'

The thing is, if I use default formatting on A11 it shows 38262 and
that is what the formulas are reading eventhou I use the correct
formatting YYYY-MM-DD .

How can I make the formulas to result in 2004, 10 and 02?

 

[Pete]

You only need to use LEFT, MID, RIGHT etc if your cells contain text
which looks like dates. If you have correct dates (which you seem to
have) you can carry out simple arithmetic on them like addition and
subtraction as in the original postings. To format the resulting cell
as I suggested, select Format | Cells | Number tab | Custom - you might
find one in the scrollable list or type your format in the box at the
top. Here's another one;

[d], hh:mm

will give you elapsed days followed by comma and hrs:minutes

Pete

 

[rantz]

Oh my god it works! Thank you so much!

One more thing. What is the diffrence between '[d], hh:mm'
and 'd, hh:mm'. I can't use the [] because excel sais it's not a but
it seems to work anyway.

Example
04-10-01 01:12:15 04-10-14 19:15:39

If I use 'd, hh:mm' I get '13, 18:03' , and that is correct.
If I use '[hh]:mm' I get '330:03' (here it is ok with [])
If I use 'hh:mm' I get '18:03'

I guess I get ALL the minutes with the []. Why can't I use the [] on d.
Can I miss information without the [] on the d?

 

[Pete]

Maybe you can only use the square brackets on the time elements - these
prevent the rounding up to the next highest category, and presumably
there is no higher category than days. The reason you get 18:03 with
just hh:mm is that you can only display 00 to 23 in the hh field, so
anything greater (i.e. your 18 days) would go into the "d" field, but
there isn't one. The square brackets prevent this from happening. You
wont miss any information by omitting [] around the d.

Pete

 

[rantz]

Thank you very much for your help, you saved the day!

 

[Pete]

That's okay, glad to be of assistance.

I saw your previous posting, but the Swedish characters put me off !
Does this now solve the problem you had then?

You also asked something about sorting - is that resolved now?

Pete

 

[rantz]

Yes, everything is good, for now ;-)

The first question was how I could combine the date and time to one
cell in order to get the diffrence. But this is much better

The sorting is no problem.

Thank you Pete!

 

[rantz]

Apparently 'd, hh:mm:ss' was no good.

For example

04-10-22 12:02:45 05-10-08 10:30:18

This results in '15, 22:27:33', if I use no formatting it results in
350,9357986

If I use 'MMD, hh:mm:ss' I get '12:15, 22:27:33' thats closer, but I
think it should be 11 months, not 12, right?

 

[Pete]

Here's another format you can try:

y "years" m "months" d "days", hh:mm

Type it in exactly as it appears above. Unfortunately this will give

0 years 0 months 4 days, 16:08

in your earlier example, so you will need to widen the column to see it
and a lot of it will be irrelevant if your elapsed time is generally
only a few days. That's why I was hoping [d] would work because this
would give 365 if the span was for a year, without bothering about the
months. I'll have a look through Help.

Pete

 

[Pete]

If you want to have the elapsed days shown as integers then you will
need to have two formulae in adjacent cells. Assuming your dates and
times are in A1 to D1 as before, try this formula in E1

= INT( C1 + D1 - A1 - B1)

and format this cell using custom => 0 "days"

Then in F1 have the same formula as before i.e.

= C1 + D1 - A1 - B1

but format this as hh:mm

Test it out by changing the dates and times to verify that this is what
you want.

Pete

 

[rantz]

Splendid, thank you! I'll try it out in the morning. Now It's off to
the pub ;-)

 

[rantz]

It is no problem to get the number of days. It displays the same result
if I use 'out-date'-'in-date' and use blank formatting.

The problem is the number of months. It always displays months +1. Even
if I use = INT( C1 + D1 - A1 - B1) and format 'M' for months.

I guess I have to live with one extra month when I use 'MMD,
hh:mm:ss'. I tried to subtract one month in the formatting of 'MMD,
hh:mm:ss' with something like 'MM[-1]D, hh:mm:ss' but this obviously
doesn't work.

 

[rantz]

The only way I can get the correct number of months is if I use
=DATEDIF((K62+M62);(O62+Q62);"YM"). But then I only get months, years
or days..

 

[Pete]

In your first posting you said this was for a warehouse - presumably
you want to track the length of time the items of stock are kept on the
shelves. What is the general duration? Is it likely to be days, or
might there be some items which are on the shelves for more than a
year? You should choose a format which is representative of the
durations. For example, there seems little point in displaying down to
seconds, or even minutes, if most items are in stock for several days.
Why try to display months, when 65 days (for example) conveys the
duration just as adequately? How long is a month anyway?

Pete

 

[rantz]

The length of time varies from secunds to years. But I have decided to
use days, like 25.1056874 days. Thatway I can use that information easy
in my simulation study. Thank you very much for your time and effort!

Pete skrev: