Deleting time part of a Date, subtracting dates

[Shirley Munro]

Hi

I am having problems subtracting dates. Ideally I would like to use the NETWORKDAYS function but the problem I am having is that the file is imported from Oracle and some of the dates include both the date and actual time, for example, 02/01/06 14:20:44. There are 2 spaces between the date and the time. I have tried a combination of the LEFT and LEN functions to remove the time but because this is a text function, it converts all the dates to text. You then need to convert them back to numbers and apply a date format. I am hoping someone can come up with a quick way of changing a date such as 14/06/06 15:23:24, to 14/06/06 so that this is the actual value that shows in the Formula Bar.

Thanks

Shirley Munro

[Ardus Petus]

=INT(A1) will strip off teh time part of A1, keeping just date

HTH
--
AP

"Shirley Munro" <[email protected]>
a écrit dans le message de news:
Shirley.Munro.29qymn_1150884602.0957...rum-nospam.com...
>
> Hi
>
> I am having problems subtracting dates. Ideally I would like to use
> the NETWORKDAYS function but the problem I am having is that the file
> is imported from Oracle and some of the dates include both the date and
> actual time, for example, 02/01/06 14:20:44. There are 2 spaces between
> the date and the time. I have tried a combination of the LEFT and LEN
> functions to remove the time but because this is a text function, it
> converts all the dates to text. You then need to convert them back to
> numbers and apply a date format. I am hoping someone can come up with
> a quick way of changing a date such as 14/06/06 15:23:24, to 14/06/06
> so that this is the actual value that shows in the Formula Bar.
>
> Thanks
>
> Shirley Munro
>
>
> --
> Shirley Munro
> ------------------------------------------------------------------------
> Shirley Munro's Profile:
> http://www.excelforum.com/member.php...nfo&userid=836
> View this thread: http://www.excelforum.com/showthread...hreadid=553979
>

[Roger Govier]

Hi Shirley

Try
=INT(A1)
assuming the data & time are held in cell A1
This will produce just the date.
Just in case you did also need to pick out the time, then
=MOD(A1,1)

--
Regards

Roger Govier


"Shirley Munro"
<[email protected]> wrote in
message
news:[email protected]...
>
> Hi
>
> I am having problems subtracting dates. Ideally I would like to use
> the NETWORKDAYS function but the problem I am having is that the file
> is imported from Oracle and some of the dates include both the date
> and
> actual time, for example, 02/01/06 14:20:44. There are 2 spaces
> between
> the date and the time. I have tried a combination of the LEFT and LEN
> functions to remove the time but because this is a text function, it
> converts all the dates to text. You then need to convert them back to
> numbers and apply a date format. I am hoping someone can come up with
> a quick way of changing a date such as 14/06/06 15:23:24, to 14/06/06
> so that this is the actual value that shows in the Formula Bar.
>
> Thanks
>
> Shirley Munro
>
>
> --
> Shirley Munro
> ------------------------------------------------------------------------
> Shirley Munro's Profile:
> http://www.excelforum.com/member.php...nfo&userid=836
> View this thread:
> http://www.excelforum.com/showthread...hreadid=553979
>

[Shirley Munro]

Thanks a lot guys. This just what I need

Shirley Munor