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

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

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?

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-----
> 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?
>
>

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)

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?

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

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?

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

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