Sorting by Date and Time

I transformed some data to an excel file. I tried to sort those data by date and time, however it didn’t work. I also tried to change the format of the date to like “mar 2 98” or “03/02/98”, but the appearance is always “3/2/98”. It just refuse to change the format. It would be appreciated if anybody could help with this.
0
plumstone (35)
6/14/2004 2:22:01 AM
excel.misc 78881 articles. 5 followers. Follow

14 Replies
544 Views

Similar Articles

[PageSpeed] 55

Hi

when you right mouse click on one of the cells, go into format / cells &
choose the number tab, click on general ... what does it say under the word
sample on the right hand side?

Cheers
JulieD

"plumstone" <plumstone@discussions.microsoft.com> wrote in message
news:78907603-1972-41E6-9DBD-C1EE121139BB@microsoft.com...
> I transformed some data to an excel file. I tried to sort those data by
date and time, however it didn't work. I also tried to change the format of
the date to like "mar 2 98" or "03/02/98", but the appearance is always
"3/2/98". It just refuse to change the format. It would be appreciated if
anybody could help with this.


0
JulieD1 (2295)
6/14/2004 2:27:15 AM
It is just the same of the natural appearance of the cell: "5/7/04" in the sample area.
If I type some new data in that cell like:"05/07/04", then in the sample area it shows 38110. And the date is going to return to normal and I can sort them by date and time or change to any format I want.
But I do not want to retype all of those date and time and I want to sort them in date, while it refuseds to be either changed in format nor sorted by date and time.

"JulieD" wrote:

> Hi
> 
> when you right mouse click on one of the cells, go into format / cells &
> choose the number tab, click on general ... what does it say under the word
> sample on the right hand side?
> 
> Cheers
> JulieD
> 
> "plumstone" <plumstone@discussions.microsoft.com> wrote in message
> news:78907603-1972-41E6-9DBD-C1EE121139BB@microsoft.com...
> > I transformed some data to an excel file. I tried to sort those data by
> date and time, however it didn't work. I also tried to change the format of
> the date to like "mar 2 98" or "03/02/98", but the appearance is always
> "3/2/98". It just refuse to change the format. It would be appreciated if
> anybody could help with this.
> 
> 
> 
0
plumstone (35)
6/14/2004 2:57:01 AM
"5/7/04"

"JulieD" wrote:

> Hi
> 
> when you right mouse click on one of the cells, go into format / cells &
> choose the number tab, click on general ... what does it say under the word
> sample on the right hand side?
> 
> Cheers
> JulieD
> 
> "plumstone" <plumstone@discussions.microsoft.com> wrote in message
> news:78907603-1972-41E6-9DBD-C1EE121139BB@microsoft.com...
> > I transformed some data to an excel file. I tried to sort those data by
> date and time, however it didn't work. I also tried to change the format of
> the date to like "mar 2 98" or "03/02/98", but the appearance is always
> "3/2/98". It just refuse to change the format. It would be appreciated if
> anybody could help with this.
> 
> 
> 
0
plumstone (35)
6/14/2004 2:59:01 AM
Hi

bad news - you need to change the format of all the cells
good news - you don't need to retype them

to sort or do anything with dates Excel needs to recognise them as dates (ie
like th 38110 one) ... somehow or other your dates have been entered as
"text" and this is why you can't do anything with them.

to change it, type the number 1 into a blank cell (that's not formatted as
text - ie it should go right aligned after you press enter)
copy it
select all the "date" cells that you need to change the format on (if they
are all consecutive, click on the first, hold down your shift key and click
on the last cell in the range - if they're not consecutive, click on the
first, hold down your ctrl key and then click on each cell you want to
include keeping the ctrl key down) ... once you have all your cells let the
shift or ctrl key go
choose EDIT / PASTE SPECIAL from the menu
choose MULTIPLY
click on the Ok button
-the cells should all go like 38110
now keep the cells selected, choose FORMAT / CELL from the menu
choose
Date (under the number tab) and select whatever format you want
Click OK
and it should now be "fixed"

let us know how you get on

Cheers
JulieD


"plumstone" <plumstone@discussions.microsoft.com> wrote in message
news:B3D2E821-831B-4316-AED6-97B66B05722A@microsoft.com...
> It is just the same of the natural appearance of the cell: "5/7/04" in the
sample area.
> If I type some new data in that cell like:"05/07/04", then in the sample
area it shows 38110. And the date is going to return to normal and I can
sort them by date and time or change to any format I want.
> But I do not want to retype all of those date and time and I want to sort
them in date, while it refuseds to be either changed in format nor sorted by
date and time.
>
> "JulieD" wrote:
>
> > Hi
> >
> > when you right mouse click on one of the cells, go into format / cells &
> > choose the number tab, click on general ... what does it say under the
word
> > sample on the right hand side?
> >
> > Cheers
> > JulieD
> >
> > "plumstone" <plumstone@discussions.microsoft.com> wrote in message
> > news:78907603-1972-41E6-9DBD-C1EE121139BB@microsoft.com...
> > > I transformed some data to an excel file. I tried to sort those data
by
> > date and time, however it didn't work. I also tried to change the format
of
> > the date to like "mar 2 98" or "03/02/98", but the appearance is always
> > "3/2/98". It just refuse to change the format. It would be appreciated
if
> > anybody could help with this.
> >
> >
> >


0
JulieD1 (2295)
6/14/2004 3:11:51 AM
Perhaps try Data > Text to columns on the column with the "dates"?

Suppose these are all in col A, A1 down

Select column A

Click Data > Text to columns

Click Next > Next to get to step 3 of the wizard

Under Column data format:
Check the button for "Date"
(the drop arrow should show "DMY"
- this is what your data looks to be like)

Click Finish

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"plumstone" <plumstone@discussions.microsoft.com> wrote in message
news:B3D2E821-831B-4316-AED6-97B66B05722A@microsoft.com...
> It is just the same of the natural appearance of the cell: "5/7/04" in the
sample area.
> If I type some new data in that cell like:"05/07/04", then in the sample
area it shows 38110. And the date is going to return to normal and I can
sort them by date and time or change to any format I want.
> But I do not want to retype all of those date and time and I want to sort
them in date, while it refuseds to be either changed in format nor sorted by
date and time.
>
> "JulieD" wrote:
>
> > Hi
> >
> > when you right mouse click on one of the cells, go into format / cells &
> > choose the number tab, click on general ... what does it say under the
word
> > sample on the right hand side?
> >
> > Cheers
> > JulieD
> >
> > "plumstone" <plumstone@discussions.microsoft.com> wrote in message
> > news:78907603-1972-41E6-9DBD-C1EE121139BB@microsoft.com...
> > > I transformed some data to an excel file. I tried to sort those data
by
> > date and time, however it didn't work. I also tried to change the format
of
> > the date to like "mar 2 98" or "03/02/98", but the appearance is always
> > "3/2/98". It just refuse to change the format. It would be appreciated
if
> > anybody could help with this.
> >
> >
> >


0
demechanik (4694)
6/14/2004 3:16:07 AM
much better solution than mine!

"Max" <demechanik@yahoo.com> wrote in message
news:%23SbHX5bUEHA.3988@tk2msftngp13.phx.gbl...
> Perhaps try Data > Text to columns on the column with the "dates"?
>
> Suppose these are all in col A, A1 down
>
> Select column A
>
> Click Data > Text to columns
>
> Click Next > Next to get to step 3 of the wizard
>
> Under Column data format:
> Check the button for "Date"
> (the drop arrow should show "DMY"
> - this is what your data looks to be like)
>
> Click Finish
>
> --
> Rgds
> Max
> xl 97
> ---
> Please respond in thread
> xdemechanik <at>yahoo<dot>com
> ----
> "plumstone" <plumstone@discussions.microsoft.com> wrote in message
> news:B3D2E821-831B-4316-AED6-97B66B05722A@microsoft.com...
> > It is just the same of the natural appearance of the cell: "5/7/04" in
the
> sample area.
> > If I type some new data in that cell like:"05/07/04", then in the sample
> area it shows 38110. And the date is going to return to normal and I can
> sort them by date and time or change to any format I want.
> > But I do not want to retype all of those date and time and I want to
sort
> them in date, while it refuseds to be either changed in format nor sorted
by
> date and time.
> >
> > "JulieD" wrote:
> >
> > > Hi
> > >
> > > when you right mouse click on one of the cells, go into format / cells
&
> > > choose the number tab, click on general ... what does it say under the
> word
> > > sample on the right hand side?
> > >
> > > Cheers
> > > JulieD
> > >
> > > "plumstone" <plumstone@discussions.microsoft.com> wrote in message
> > > news:78907603-1972-41E6-9DBD-C1EE121139BB@microsoft.com...
> > > > I transformed some data to an excel file. I tried to sort those data
> by
> > > date and time, however it didn't work. I also tried to change the
format
> of
> > > the date to like "mar 2 98" or "03/02/98", but the appearance is
always
> > > "3/2/98". It just refuse to change the format. It would be appreciated
> if
> > > anybody could help with this.
> > >
> > >
> > >
>
>


0
JulieD1 (2295)
6/14/2004 3:24:46 AM
There are several ways to convert text numbers/dates to real numbers/dates:

   http://www.contextures.com/xlDataEntry03.html

plumstone wrote:
> It is just the same of the natural appearance of the cell: "5/7/04" in the sample area.
> If I type some new data in that cell like:"05/07/04", then in the sample area it shows 38110. And the date is going to return to normal and I can sort them by date and time or change to any format I want.
> But I do not want to retype all of those date and time and I want to sort them in date, while it refuseds to be either changed in format nor sorted by date and time.
> 
> "JulieD" wrote:
> 
> 
>>Hi
>>
>>when you right mouse click on one of the cells, go into format / cells &
>>choose the number tab, click on general ... what does it say under the word
>>sample on the right hand side?
>>
>>Cheers
>>JulieD
>>
>>"plumstone" <plumstone@discussions.microsoft.com> wrote in message
>>news:78907603-1972-41E6-9DBD-C1EE121139BB@microsoft.com...
>>
>>>I transformed some data to an excel file. I tried to sort those data by
>>
>>date and time, however it didn't work. I also tried to change the format of
>>the date to like "mar 2 98" or "03/02/98", but the appearance is always
>>"3/2/98". It just refuse to change the format. It would be appreciated if
>>anybody could help with this.
>>
>>
>>
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
6/14/2004 3:31:35 AM
Hi JulieD,
Thanks a lot. It is going to save me a lot of my time in the next few weeks.
I just signed in tonight and this is my first question. And I am so glad to get your prompt help.
Plumstone :)

"JulieD" wrote:

> Hi
> 
> bad news - you need to change the format of all the cells
> good news - you don't need to retype them
> 
> to sort or do anything with dates Excel needs to recognise them as dates (ie
> like th 38110 one) ... somehow or other your dates have been entered as
> "text" and this is why you can't do anything with them.
> 
> to change it, type the number 1 into a blank cell (that's not formatted as
> text - ie it should go right aligned after you press enter)
> copy it
> select all the "date" cells that you need to change the format on (if they
> are all consecutive, click on the first, hold down your shift key and click
> on the last cell in the range - if they're not consecutive, click on the
> first, hold down your ctrl key and then click on each cell you want to
> include keeping the ctrl key down) ... once you have all your cells let the
> shift or ctrl key go
> choose EDIT / PASTE SPECIAL from the menu
> choose MULTIPLY
> click on the Ok button
> -the cells should all go like 38110
> now keep the cells selected, choose FORMAT / CELL from the menu
> choose
> Date (under the number tab) and select whatever format you want
> Click OK
> and it should now be "fixed"
> 
> let us know how you get on
> 
> Cheers
> JulieD
> 
> 
> "plumstone" <plumstone@discussions.microsoft.com> wrote in message
> news:B3D2E821-831B-4316-AED6-97B66B05722A@microsoft.com...
> > It is just the same of the natural appearance of the cell: "5/7/04" in the
> sample area.
> > If I type some new data in that cell like:"05/07/04", then in the sample
> area it shows 38110. And the date is going to return to normal and I can
> sort them by date and time or change to any format I want.
> > But I do not want to retype all of those date and time and I want to sort
> them in date, while it refuseds to be either changed in format nor sorted by
> date and time.
> >
> > "JulieD" wrote:
> >
> > > Hi
> > >
> > > when you right mouse click on one of the cells, go into format / cells &
> > > choose the number tab, click on general ... what does it say under the
> word
> > > sample on the right hand side?
> > >
> > > Cheers
> > > JulieD
> > >
> > > "plumstone" <plumstone@discussions.microsoft.com> wrote in message
> > > news:78907603-1972-41E6-9DBD-C1EE121139BB@microsoft.com...
> > > > I transformed some data to an excel file. I tried to sort those data
> by
> > > date and time, however it didn't work. I also tried to change the format
> of
> > > the date to like "mar 2 98" or "03/02/98", but the appearance is always
> > > "3/2/98". It just refuse to change the format. It would be appreciated
> if
> > > anybody could help with this.
> > >
> > >
> > >
> 
> 
> 
0
plumstone (35)
6/14/2004 3:39:01 AM
um .. not really ..
imho, any solution that works is good <g>
... but thanks for the feedback, JulieD !

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
news:OpqKL9bUEHA.2940@TK2MSFTNGP09.phx.gbl...
> much better solution than mine!


0
demechanik (4694)
6/14/2004 3:41:12 AM
Thanks for all of you.
It works!!!
Plumstone :)

"plumstone" wrote:

> I transformed some data to an excel file. I tried to sort those data by date and time, however it didn’t work. I also tried to change the format of the date to like “mar 2 98” or “03/02/98”, but the appearance is always “3/2/98”. It just refuse to change the format. It would be appreciated if anybody could help with this.
0
plumstone (35)
6/14/2004 3:43:01 AM
Always glad to hear that !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"plumstone" <plumstone@discussions.microsoft.com> wrote in message
news:EE483900-5358-43FB-B9D5-68B9BF424A17@microsoft.com...
> Thanks for all of you.
> It works!!!
> Plumstone :)


0
demechanik (4694)
6/14/2004 4:03:31 AM
Max,
Thanks.
Plumstone :)

"Max" wrote:

> Perhaps try Data > Text to columns on the column with the "dates"?
> 
> Suppose these are all in col A, A1 down
> 
> Select column A
> 
> Click Data > Text to columns
> 
> Click Next > Next to get to step 3 of the wizard
> 
> Under Column data format:
> Check the button for "Date"
> (the drop arrow should show "DMY"
> - this is what your data looks to be like)
> 
> Click Finish
> 
> --
> Rgds
> Max
> xl 97
> ---
> Please respond in thread
> xdemechanik <at>yahoo<dot>com
> ----
> "plumstone" <plumstone@discussions.microsoft.com> wrote in message
> news:B3D2E821-831B-4316-AED6-97B66B05722A@microsoft.com...
> > It is just the same of the natural appearance of the cell: "5/7/04" in the
> sample area.
> > If I type some new data in that cell like:"05/07/04", then in the sample
> area it shows 38110. And the date is going to return to normal and I can
> sort them by date and time or change to any format I want.
> > But I do not want to retype all of those date and time and I want to sort
> them in date, while it refuseds to be either changed in format nor sorted by
> date and time.
> >
> > "JulieD" wrote:
> >
> > > Hi
> > >
> > > when you right mouse click on one of the cells, go into format / cells &
> > > choose the number tab, click on general ... what does it say under the
> word
> > > sample on the right hand side?
> > >
> > > Cheers
> > > JulieD
> > >
> > > "plumstone" <plumstone@discussions.microsoft.com> wrote in message
> > > news:78907603-1972-41E6-9DBD-C1EE121139BB@microsoft.com...
> > > > I transformed some data to an excel file. I tried to sort those data
> by
> > > date and time, however it didn't work. I also tried to change the format
> of
> > > the date to like "mar 2 98" or "03/02/98", but the appearance is always
> > > "3/2/98". It just refuse to change the format. It would be appreciated
> if
> > > anybody could help with this.
> > >
> > >
> > >
> 
> 
> 
0
plumstone (35)
6/14/2004 4:06:01 AM
Pleasure', Plumstone !
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"plumstone" <plumstone@discussions.microsoft.com> wrote in message
news:77C14CE5-71D8-46F7-B681-8BDCBD78BA08@microsoft.com...
> Max,
> Thanks.
> Plumstone :)


0
demechanik (4694)
6/14/2004 4:07:37 AM
Because data in these cells are text type, so you couldn't sort it. You need 
convert them to date-time type.
You could download the third party tool: AddinTools Assist from 
http://www.addintools.com. It include several functions. One of these 
functions is convert data of cells to text/number/date-time type. 
It can convert data of all formats to text type.
It can recognize these formats as following and convert to date-time type: 
2003.1.2, 2003.1.2 11:12:13:14PM, 2003-1-2, 2003-1-2 11:12:13.14PM, 
20030102, 20030102 11:12:13:14PM,  and locale format on your computer, and so 
on …

0
8/13/2004 9:01:02 AM
Reply:

Similar Artilces:

Daylight Savings Time Changes
Hello, Does anyone know if the patch for Exchnage 2003 identified in KB926666 has to also be applied to every server that is running the Exchange Tools (System Manager)? Thanks in advance K On Wed, 31 Jan 2007 10:24:03 -0800, Kerry <Kerry@discussions.microsoft.com> wrote: >Hello, > >Does anyone know if the patch for Exchnage 2003 identified in KB926666 has >to also be applied to every server that is running the Exchange Tools (System >Manager)? > >Thanks in advance >K Im all for consistency. If the patch isnt needed, it shouldnt install, so I would at ...

How to sort a list correctly?
HI, whenever I sort my list in excel it all gets messed up. What am I doing wrong? Is there something I can do to stop this happening? THanks in advance Melani Hi try selecting the entire data range before sorting (Use 'Data - Sort' for this) -- Regards Frank Kabel Frankfurt, Germany "Melani" <Melani@discussions.microsoft.com> schrieb im Newsbeitrag news:13CB1140-7906-4AB4-ABE4-D6F4AC7B2D32@microsoft.com... > HI, > > whenever I sort my list in excel it all gets messed up. What am I doing wrong? > > Is there something I can do to stop this happening...

Is there a way to Sort Data by font color or cell shading?
I have a long list of items that I sort by either item number or date depending on what I'm looking for. From time to time these items status will change and I will gray them out by just shading the background of the cell gray. Is there a way I can sort these items by the background/shading of the cell first and then the secondary sort be date? So all the cells which contain gray shading will be first in the list (sorted by date if possible) then the rest of the list without the gray shading will follow by date as well. Thanks. Take a look at this link: http://www.cpearson.c...

Date on Template
I created some templates and am choosing to bring over a date to be shown in the template when it is run. I have selected this attribute to be date only and not date & time. Every time I run the template it still shows date+time+time zone. Is there a way to show only the date and not the rest of the data. Thanks for any help anyone can provide. ...

Alpha sort names/numericals
I have 2 columns. 1st column has names, 2nd has numerical values relevant to the names. How can this data be alpha sorted and keep the same name/numerical data. data/sort "dane" <dane@discussions.microsoft.com> wrote in message news:A9565E66-9FC0-48BF-A06B-FF51E184A8A1@microsoft.com... > I have 2 columns. 1st column has names, 2nd has numerical values relevant to > the names. How can this data be alpha sorted and keep the same name/numerical > data. Hi Dane, Please state clearly what the problem is. If you sorted and got the names sorted and the numbers did not...

Calculate Dates with If Then Else Statements
I need to calculate total years’ full time experience (i.e. 2.5 years, 3.25 years, etc.) in an Access 2003 database form. This is what I am trying to do: If the Full-Time End Date is Blank, use Today's Date, ELSE if the Full-Time End Date has a Date, use that Date. Separately, these two formulas work: =DateDiff("m",[FullTimeStartDate],Now())/12 =DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12 My problem is writing a statement to calculate using the END DATE if one is entered, or NOW, if there is no end date. I have tried many combinations, but can’t ge...

sort data
i am trying, unsuccessfullty, to sort some data (a small example is below) so I can graph it. I want to put it into a pivot table format but don't want calculations, just the actual data. any ideas??/ Location DATE Result WAT002WA ALBANY BOTTOM 19-Jul-05 0.83 WAT002WA ALBANY BOTTOM 23-Jun-05 0.8 WAT002WA ALBANY BOTTOM 28-May-05 1.16 WAT002WA ALBANY BOTTOM 02-May-05 0.93 WAT002WA ALBANY BOTTOM 06-Apr-05 0.66 WAT002WA ALBANY BOTTOM 11-Mar-05 0.59 WAT002WA ALBANY BOTTOM 13-Feb-05 0.54 WAT002WA ALBANY BOTTOM 18-Jan-05 0.62 WAT002WA ALBANY TOP 09-Mar-07 0.73 ...

Outlook locks up all the time
This is a shot in the dark. Ever since getting my new Thinkpad with Windows XP, Outlook has never seemed to work properly. I've run diagnostics on my machine and nothing comes up as a problem. I've installed a spyware program because after talking to IBM tech support, they suggested I try that. I also talked to another support tech, who suggested I reinstall Windows completely. I'm not a real technical person. Won't a re-install wipe out my files? Most of the time that I use Outlook for my email, it slows down and eventually locks up. When I restart my computer,...

Running macros for different cells at the same time
Hi all, Is it possible to run different macros on different cells at the sam time? If so how do I go about setting it up? Cheers Michae -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to do. Could you give an example? -- Regards Frank Kabel Frankfurt, Germany > Hi all, > > Is it possible to run different macros on different cells at the same > time? If so how do I go about setting it up? > > Cheers > > Michael > > > --- > Message posted from http://www.ExcelForum.com/ Yes it is possible to run different m...

Filter Date
Hi, I have a form which contains a listbox (holding the values Process Date and Quantity) and I have a textbox which displayed the current month. What I want to do is when I click on a command button (cmdMonthDown) and the textbox value changes from May to April I want the values in the listbox to be filtered so that only those values are disiplayed that were entered in April. If I'm not making sense please let me know otherwise your help would be appreciated. Thanks Reference the text box as a parameter in the list box's RowSourcee, e.g. if the text box shows the month name in fu...

Task start/end dates from MS project to Workspace site
We are currently running MSPS 2007 with WSS 3.0. I am trying to create a list in one of the project work spaces. I would like to auto-populate two columns in that list with the start and end date of certain tasks from the Project Plan in MS project. I am thinking we can do this by linking these columns to the the project SQL database fields. So, 1) Do I use the published SQL databse for this? 2) Which fields in the SQL database would I have to link to get the Start and End dates? EggHeadCafe - Software Developer Portal of Choice AutoList in ASP.Net http://www.eggheadcafe.com/tutorial...

Date Format when incoming date can be 0
I have a date fiels yyyymmdd which can have a value of 0 or a valid date in recent past. Excel displays the 0 date as 01/01/1900 and the non zero dates as expected. Cant figure this out , any help would be appreciated. Excel uses a sequencial number from either 1/1/1900, or 1/1/1904 (depending on your options) as the date value for Example 6/3/2005 is 38506 using the 1/1/1900 if the cell is formatted as a date it is responding with the date it thinks you mean. If you want the cell to display a zero =if(Date=0,"0",date) "DP NY10601" wrote: > I have a date fiels y...

how to calculate the substraction between two dates
hi, i have two cells ,in which there are two dates. (for instance ,one cel l 08/02 ,the other 07/30) if the gap of the two dates is 2 days , something will be set to do. but it is difficult to judge the the substraction between two dates any good ideas. thanks Perhaps this might be of help .. Assume the *dates* "2-Aug-2004" and "30-Jul-2004" are input in A1 and B1, and A1:B1 is custom formatted as: mm/dd viz. it'll show as: In A1: 08/02 In B1: 07/30 Try in C1: =DAY(ABS(A1-B1))-1 Format C1 as: General or Number C1 will return: 2 i.e. the number of days...

Date Range
Hi, When I want the date rage appeared on the report header, I use textbox and put (qry criteria) in its recordsource. It works fine. However, For this time, I got a message like this ‘First([Between [From this date] And [To this date]])" Is there something wrong with the qry criteria? Would you please show me how to fix the problem? Thanks Chi It's very hard to understand the crtieria, can you post the full SQL What is the First in the beginning? Why there are to square brackes in the end And in the beinning? First([Between [From this date] And [To this date]])" T...

Sorting imported text
I have a spreadsheet into which I've imported a great deal of data in two columns only. I set out rows one, three and five of hundreds of such rows. The cells in column A consist of place names. (The "C" following the place name stands for "City", the "A" for "Area".) The cells in column C represent the population of the city or area, as the case may be. I've formatted the cells in column C as numbers. Albury (C) 44887 Armidale Dumaresq (A) 24596 Ashfield (A) 40258 I want to sort the placenames in order of their po...

download the Credit Card transaction date not the the posting date
Then I download a statement from a Credit card and import it in to MS Money I always get the Posting Date not the Transaction date. Do I need another column in the register, help does not tell me how to do that? Or is the only solution changing each entry by hand? ( that doesn't make sense) This credit card offers downloads in either QIF or spreadsheet. In microsoft.public.money, riskymanr wrote: >Then I download a statement from a Credit card and import it in to MS Money I >always get the Posting Date not the Transaction date. Do you mean you first enter the transactions by...

How do I convert a time format to a double
I use a function to calculate the hours worked based on time 12:00pm to 4:00pm is 4.0 hours when I get the number for hours worked it is in time format / .27398237 I need the number 4 so that I can do my calculation. How do I convert it to a number? Multiply with 24 and format as general -- Regards, Peo Sjoblom "DMB" <DMB@discussions.microsoft.com> wrote in message news:552FAD83-E8D4-4573-B9FF-613DDD259C22@microsoft.com... > I use a function to calculate the hours worked based on time > > 12:00pm to 4:00pm is 4.0 hours > > when I get the number for hou...

If Formula , Adding Days to a Date
A B 1 06/06/10 I want to make a formula that calculate as following; in Cell (B1) if the date in future the result will be [Enrolled], if not it will be Cell (A1) + 730 Days On Sat, 27 Mar 2010 02:14:01 -0700, Khalid A. Al-Otaibi <KhalidAAlOtaibi@discussions.microsoft.com> wrote: > A B >1 06/06/10 > >I want to make a formula that calculate as following; > >in Cell (B1) if the date in future the result will be [Enrolled], if not it >will be Cell (A1) + 730 Days Try this formula in cell B1: =IF(A1>TODAY(), "[En...

lookup a date from an array of date ranges if conditions are met
Please help...i need to lookup up a value on a specific date range. for example: column A: column b (from 9/01/2003 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Sounds like autofilter could be something, select the data (should have headers), do data>filter>autofilter, filter on the date column using custom and gate than or equal to and put 09/01/03 as a criteria -- Regards, Peo Sjoblom "nscanceran" <nscanceran.wj1qn@excelforum-no...

Release Date of V 4.0
Hi Does any one know the official release date for CRM V 4.0? Will CRM V 4.0 be available for partners before it is available for the general public? Thanks alot ...

looking to sort my personal address book
trying to find how I can sort my personal address book by last name, comma, first name Are you really using a Personal Address Book? Why? It was replaced by the Outlook Address Book 6 years ago. The PAB cannot be sorted by Last, First because both names are in the same field. -- Russ Valentine [MVP-Outlook] "Glen" <anonymous@discussions.microsoft.com> wrote in message news:02b101c3b902$c54cbe40$a101280a@phx.gbl... > trying to find how I can sort my personal address book by > last name, comma, first name Russ, all my contacts are in "my personal address book&qu...

TIME function used in Excel
I'm new to these newsgroups, so I apologize if my etiquette is not appropriate. I'm trying to help a friend whose computer was recently replaced. In his new computer, his custom macro will not run. He gets a "Microsoft Visutal Basic Compile error: Can't find project or library" with the TIME function highlighted. I first suggested using the NOW or DATE function, but he said it creates an additional series of errors. I also tried installing 'Windows Script 5.6' to no avail. Any suggestions on how to get this TIME function to work? Thank you in advance for ...

Sort data Excel 2007
How to sort using date and then time in an "a" and "p" format for Excel A normal sort should to what you want. Nothing special here... Bob Umlas Excel MVP "Billy Bob" wrote: > How to sort using date and then time in an "a" and "p" format for Excel Bob, I entered the dates under "date" and the time under "time". The time was entered as 10:00a - 2:00p - and 1:00p but it did not sort them. The date came out correct but I added another level and the sort asked for A - Z. I finaly went to military time and that worke...

Date and Time formular
I'm trying to take one date and time away from another but having a problem fathomining it out eg 01.04.2008 10:55:27 - 01.04.2008 9:37:13 The date and time are in the same cell eg 01.04.2008 10:55:27 is in A1 A formula for this would be great if anyone has one to hand Thanks Just subtract =End_Time-Start_Time -- Regards, Peo Sjoblom "SS" <Student.2@uk.bosch.com> wrote in message news:g0hgsk$jgc$1@news4.fe.internet.bosch.com... > I'm trying to take one date and time away from another but having a > problem fathomining it out > > eg >...

Sorting #8
I need to sort a column in desending order so the first number is 121, 120, etc. down to 1. Some numbers have an alpha (68a, 68b) but I still want them in the correct order. Right now the cell formating is "general." How do I accomplish? Thanks! The answer you get is not likely to be better than your description. So all I can say is see if this solves your question. Create a helper column to extract the digits, and sort on that as a descending number. Then sort on the original column which is text in ascending or descending order depending on if 68a is to come before 68b ...