Date Dilemna

[Lambi000]

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)

 

[Mattlynn via OfficeKB.com]

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

 

[Rick Rothstein]

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

 

[David Biddulph]

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.

 

[Lambi000]

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