I get a report forwarded by an outside vendor that comes in Excel format. In
the date cell the date format is like 01-Sep-08. The time is in a separate
cell and is in military format like 18:53:14. Another field has the date and
time combined in one cell and is in the same format as described above.

I need to subtract the dates and times in the two cells from the dates and
times in the one cell. Firstly, I can't seem to convert the dates into
something that isn't text and, secondly, I can't seem to combine the cells so
they represent something numeric that can be used in a formula.

Is it possible to do what I want or should I look at Access or some other
solution to help me do this? (Well, it's not really what I want but what my
boss wants:))

Hi
Assuming date and time combined are in A1.

In b1 you can type =day(a1) This will give you Day as a number
In c1 you can type =month(a1) This will give you Month
In d1 you can type =year(a1) This will give you year

from this, you can build good pivots etc

Thanks
Matt




Lambi000 wrote:
I get a report forwarded by an outside vendor that comes in Excel format. In
the date cell the date format is like 01-Sep-08. The time is in a separate
cell and is in military format like 18:53:14. Another field has the date and
time combined in one cell and is in the same format as described above.

I need to subtract the dates and times in the two cells from the dates and
times in the one cell. Firstly, I can't seem to convert the dates into
something that isn't text and, secondly, I can't seem to combine the cells so
they represent something numeric that can be used in a formula.

Is it possible to do what I want or should I look at Access or some other
solution to help me do this? (Well, it's not really what I want but what my
boss wants:))

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200809/1

Simply add them (if A1 is the date and B1 is the time, then A1+B1 will be a
date/time).

--
Rick (MVP - Excel)


"Lambi000" wrote in message
...
I get a report forwarded by an outside vendor that comes in Excel format.
In
the date cell the date format is like 01-Sep-08. The time is in a
separate
cell and is in military format like 18:53:14. Another field has the date
and
time combined in one cell and is in the same format as described above.

I need to subtract the dates and times in the two cells from the dates and
times in the one cell. Firstly, I can't seem to convert the dates into
something that isn't text and, secondly, I can't seem to combine the cells
so
they represent something numeric that can be used in a formula.

Is it possible to do what I want or should I look at Access or some other
solution to help me do this? (Well, it's not really what I want but what
my
boss wants:))

If you've got 01-Sep-08 as text and you want to convert it to a real date,
try Data/ Text to columns/ ... & select DMY as the format as you get towards
the end of the wizard. You can do the same if you have 01-Sep-08 18:53:14
as text.

You may, however be able to get away with merely adding your time to the
text date, as Excel will often convert the text to a date at that stage.
Format the result as dd-mmm-yy hh:mm:ss and Excel will happily do arithmetic
with it.

If Excel is reluctant to convert your text to dates, it may be that the text
includes non-breaking spaces or other non-printing characters. You may need
to clean the data up before Excel can understand it.
--
David Biddulph

"Lambi000" wrote in message
...
I get a report forwarded by an outside vendor that comes in Excel format.
In
the date cell the date format is like 01-Sep-08. The time is in a
separate
cell and is in military format like 18:53:14. Another field has the date
and
time combined in one cell and is in the same format as described above.

I need to subtract the dates and times in the two cells from the dates and
times in the one cell. Firstly, I can't seem to convert the dates into
something that isn't text and, secondly, I can't seem to combine the cells
so
they represent something numeric that can be used in a formula.

Is it possible to do what I want or should I look at Access or some other
solution to help me do this? (Well, it's not really what I want but what
my
boss wants:))

Thanks all! I decided to do it with Access since I'm much more proficient in
that than in Excel. Got it working just the way I want with Access. But
thanks for your help.

Lambi

"David Biddulph" wrote:

If you've got 01-Sep-08 as text and you want to convert it to a real date,
try Data/ Text to columns/ ... & select DMY as the format as you get towards
the end of the wizard. You can do the same if you have 01-Sep-08 18:53:14
as text.

You may, however be able to get away with merely adding your time to the
text date, as Excel will often convert the text to a date at that stage.
Format the result as dd-mmm-yy hh:mm:ss and Excel will happily do arithmetic
with it.

If Excel is reluctant to convert your text to dates, it may be that the text
includes non-breaking spaces or other non-printing characters. You may need
to clean the data up before Excel can understand it.
--
David Biddulph

"Lambi000" wrote in message
...
I get a report forwarded by an outside vendor that comes in Excel format.
In
the date cell the date format is like 01-Sep-08. The time is in a
separate
cell and is in military format like 18:53:14. Another field has the date
and
time combined in one cell and is in the same format as described above.

I need to subtract the dates and times in the two cells from the dates and
times in the one cell. Firstly, I can't seem to convert the dates into
something that isn't text and, secondly, I can't seem to combine the cells
so
they represent something numeric that can be used in a formula.

Is it possible to do what I want or should I look at Access or some other
solution to help me do this? (Well, it's not really what I want but what
my
boss wants:))