Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove Time from DateTime
Hello...
I need to know if there is an easy way to change the format of the data I have imported into Excel from a DateTime stamp to just a Date stamp. I am importing data from a Microsoft Dynamics CRM 4.0 report, and the fields in the report are DateTime fields in the CRM application. The data that is being imported looks as follows: 8/7/2008 10:00:00 PM In my workbook, I have formula calculations that compare two dates, less than or equal to, and the time stamp is throwing off the results when the dates are the same... It is causing the 'equal to' calculation to come back false instead of true. So, what I need is a good way to strip out the Time from the data, and just include the Date. Is this possible? Thank you in advance for any help!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove Time from DateTime
=int(A2)
Note that you said: "... change the format of the data I have imported into Excel from a DateTime stamp to just a Date stamp. " but it looks as if you want to change the *content* of the data, not just the *format*. -- David Biddulph wrote in message ... Hello... I need to know if there is an easy way to change the format of the data I have imported into Excel from a DateTime stamp to just a Date stamp. I am importing data from a Microsoft Dynamics CRM 4.0 report, and the fields in the report are DateTime fields in the CRM application. The data that is being imported looks as follows: 8/7/2008 10:00:00 PM In my workbook, I have formula calculations that compare two dates, less than or equal to, and the time stamp is throwing off the results when the dates are the same... It is causing the 'equal to' calculation to come back false instead of true. So, what I need is a good way to strip out the Time from the data, and just include the Date. Is this possible? Thank you in advance for any help!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove Time from DateTime
You need to realize that dates are stored internally as the number of
days passed since some reference date (hence they are always whole numbers), and times are stored as fractions of a 24-hour day. Thus, to strip away the time element all you need to do is: =INT(A1) where A1 contains the date/time. Alternatively, you can incorporate the INT function in your formula, so that you are comparing only dates. Hope this helps. Pete On Sep 18, 8:25*pm, wrote: Hello... I need to know if there is an easy way to change the format of the data I have imported into Excel from a DateTime stamp to just a Date stamp. *I am importing data from a Microsoft Dynamics CRM 4.0 report, and the fields in the report are DateTime fields in the CRM application. *The data that is being imported looks as follows: 8/7/2008 *10:00:00 PM In my workbook, I have formula calculations that compare two dates, less than or equal to, and the time stamp is throwing off the results when the dates are the same... It is causing the 'equal to' calculation to come back false instead of true. So, what I need is a good way to strip out the Time from the data, and just include the Date. *Is this possible? *Thank you in advance for any help!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove Time from DateTime
Hi,
If it's a properly formatted date/time then simply reformat the cell as date. If it's not then post back. Mike " wrote: Hello... I need to know if there is an easy way to change the format of the data I have imported into Excel from a DateTime stamp to just a Date stamp. I am importing data from a Microsoft Dynamics CRM 4.0 report, and the fields in the report are DateTime fields in the CRM application. The data that is being imported looks as follows: 8/7/2008 10:00:00 PM In my workbook, I have formula calculations that compare two dates, less than or equal to, and the time stamp is throwing off the results when the dates are the same... It is causing the 'equal to' calculation to come back false instead of true. So, what I need is a good way to strip out the Time from the data, and just include the Date. Is this possible? Thank you in advance for any help!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove Time from DateTime
hi
is a helper column put this =DATE(YEAR(A4),MONTH(A4),DAY(A4)) copy down as far as you need. copy the helper column and pastespecaial values over the old data. adjust cell reference to suit. Regards FSt1 " wrote: Hello... I need to know if there is an easy way to change the format of the data I have imported into Excel from a DateTime stamp to just a Date stamp. I am importing data from a Microsoft Dynamics CRM 4.0 report, and the fields in the report are DateTime fields in the CRM application. The data that is being imported looks as follows: 8/7/2008 10:00:00 PM In my workbook, I have formula calculations that compare two dates, less than or equal to, and the time stamp is throwing off the results when the dates are the same... It is causing the 'equal to' calculation to come back false instead of true. So, what I need is a good way to strip out the Time from the data, and just include the Date. Is this possible? Thank you in advance for any help!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove Time from DateTime
On Sep 18, 1:42*pm, FSt1 wrote:
hi is a helper column put this =DATE(YEAR(A4),MONTH(A4),DAY(A4)) copy down as far as you need. copy the helper column and pastespecaial values over the old data. adjust cell reference to suit. Regards FSt1 " wrote: Hello... I need to know if there is an easy way to change the format of the data I have imported into Excel from a DateTime stamp to just a Date stamp. *I am importing data from a Microsoft Dynamics CRM 4.0 report, and the fields in the report are DateTime fields in the CRM application. *The data that is being imported looks as follows: 8/7/2008 *10:00:00 PM In my workbook, I have formula calculations that compare two dates, less than or equal to, and the time stamp is throwing off the results when the dates are the same... It is causing the 'equal to' calculation to come back false instead of true. So, what I need is a good way to strip out the Time from the data, and just include the Date. *Is this possible? *Thank you in advance for any help!!- Hide quoted text - - Show quoted text - Thank you to everyone!! Got it working and embedded the INT function in the comparison formula. Thank you all for helping out so quickly... I truly appreciate it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting datetime from character string | Excel Worksheet Functions | |||
pop up calendar using MS DateTime Picker | Excel Worksheet Functions | |||
MS Query DateTime String | Excel Worksheet Functions | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions | |||
autodate and datetime stamp | Excel Worksheet Functions |