Convert Date and Time to an unique number

[CheopsChefren]

Just new here, and i guess i have a simple question.
I have a sheet with columns a to n. In column c, all filled rows, is a date, and in column d , all filled rows, is a time.
I want to be able to convert those 2 cells to 1 unique number.
I want this as i have to create a new sheet based on startdate/starttime - enddate/endtime, for example all rows where date and time are between 12-12-2008/08:12:21 and 18-12-2008 21:37:11 (i use european date/time notation) should be copied to a new sheet, and if the dates/time could be converted to unique numbers this would be much easier to implement.
Especially as i have never used any programming before, and i am all new on how to achieve any at all. Any help/advice would be greatly appreciated.

 

[mintjulep]

Take a look at faq68-5827

You will see that with Excel's method of time keeping, each and every instant in time is represented by a unique number.

 

[SkipVought]

Hi,

May I ask why you need, "to create a new sheet based on startdate/starttime - enddate/endtime"? What is the purpose of having separate sheets? Doing so, severely defeats MANY of the features that Excel offers.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CheopsChefren]

I need 3 new sheets in fact. One sheet with the original data without duplicates, one sheet where column j = 0 and one sheet where column j not 0. And they have to be seperate sheets, because they have to be emailed to different departments, but all those departments just want to see the data which is for them. So department 1 needs sheet a, etc.

I will check/read the FAQ mentioned above, and hope it does help me.

Thanks sofar for the answers.

 

[SkipVought]

You can use faq68-5829 to return a resultset defined by your criteria.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CheopsChefren]

I have read the FAQ, and it does answer some of my questions.. But I still am puzzled on how to use it?
As mentioned cell Cx contains a date and cell Dx contains a time.
I guess i need to create strings from the values in both cells and concatenate them with a comma or dot to keep both values distinguisable, then convert that string back to a number. Then loop through my original sheet, and if "Value("string Cx" + "string Dx")" is between "Value("Date as entered by user" + "," + ("Time as entered by user") copy that entire row to another sheet.
Wont that be a "time consuming" way of doing things? Because i have to do the converting for every row in the sheet, which can consist of thousands of rows.

 

[SkipVought]

"I guess i need to create strings from the values in both cells and concatenate them with a comma or dot to keep both values distinguisable, then convert that string back to a number."

No, just ADD the two values, assuming that they are both REAL DAte/Time values.

You can QUERY the results in another sheet. No VBA required.
[tt]
Select whatever columns you want
From [YourSheetName$]
Where [YourDateTime_FieldName] Between #12-12-2008/08:12:21# And #18-12-2008 21:37:11#
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CheopsChefren]

I think though FAQ68-5829 does help in some ways, its not what i really want. We have to do above mentioned procedure many times a day, always with different sheets, and different dates/times etc. Which means the query has to change many times a day, and the users who need to work with it, not all want to do that every time they use the sheet. They are mainframe operators, and a good mainframe operator is a lazy person

 

[SkipVought]

It can be a Parameter query that uses a Cell Value as a value for a criteria.

then it's as simple as,

Enter new limits in two cells
refersh the query Data > Refresh

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CheopsChefren]

Ok, so the time will always be between 0.00 and 1.00?

 

[SkipVought]

Time is >=0 and <1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CheopsChefren]

But doing it like that, with the Query, u cant get the values in different sheets i think?
Anyway , u do a select where date/time between date/time1 and date/time2 where column j = 0 and a same select where column j not 0 ?

 

[SkipVought]

Yes.

Please post

1. A sample of your source table

2. the SQL that you are using in MS Query. (See the SQL Button in the Query By Example [QBE] grid)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CheopsChefren]

I will do that later today: i have to create a sheet. I have one problem at the moment: for the query to work, the sheet u base ure query on has to be formatted as a table. I tried doing that via the Data-Table, but cant seem to get that correct. It asks about the row and column. What should I answer there? If I answer row: $1$1 and column $a$a or whatever else possible combination, it doesnt allow me to.

 

[CheopsChefren]

grgrgrrg... One simple question. How can i see what is the number associated with a specific date? So how can i see in excel what number 12-12-2008 is? What function do i need for that?

 

[SkipVought]

for the query to work, the sheet u base ure query on has to be formatted as a table"

What is a TABLE

1. ONE row of unique headings in ROW 1 starting in Column A.

2. Best if there are no empty rows or columns in your table (ie all data and headings are contiguous)

3. each column must had similar related data.

Its that simple.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

So how can i see in excel what number 12-12-2008 is? What function do i need for that?

Assuming that has been entered as text:
DateValue
You probably want a result in your table of DateValue(...)+TimeValue(...)

Another (easer to put into code) way is select the date column then
Data,TexttoColumns.......
Specify as a Date field

If original entered as a proper date then just change the format of the column.



Gavin

 

[macropod]

Hi CheopsChefren,

If you use Excel's Autofilter function (Data|Filter|AutoFilter) on Col J, that will allow you to show 'all', '0', '1', etc. A custom filter is also available that allows you to show all values not equal to 0, for example.

Using that approach, you can send each Dept the same workbook, just filtered differently.

Cheers

[MS MVP - Word]