Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting time part of a Date, subtracting dates
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting time part of a Date, subtracting dates
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" wrote in message news:Shirley.Munro.29qymn_1150884602.0957@excelfor um-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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting time part of a Date, subtracting dates
Thanks a lot guys. This just what I need Shirley Munor -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determining Date X based on Other dates | Excel Worksheet Functions | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
How to use solver to schedule part time and full time workforce? | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Date and Time Macro | Excel Discussion (Misc queries) |