Need Help with sorting

[Shaminder Singh]

I am trying to sort my data by date and time but somehow excel does not recognize time. Please help.

 

[bobhc]

Good day Shaminder

Your times and numbers were not format as such just general.
Turn your data in to a table for easy filtering see up-load.
And then turn it into a pivot table.
By using a table for the pivot any new data added will update the pt on refresh.

 

[Shaminder Singh]

Hi,

Data is still not sorted by time. Am I missing something here?

Thanks,
Shaminder

 

[Xiq]

Well, there is a space in front of the time-stamp. Because of this, Excel see this as text.

 

[Xiq]

What you can do is:

• Select column J
• Press Ctrl + F
• Go to the Replace-tab
• In the "Find What"-box, place a single space charactar [spacebar]
• In the "Replace With"-box, remove every thing (make sure )
• Press the "Replace All"-button
• ...
• Profit!

Now you can try to sort


Edit:
PS - if you plan to use the pivot table, you need to refresh the data --> select the table and press ALT + F5

 

[Shaminder Singh]

Thanks Xiq.

I have more question.

I need to create a lookup formula for these conditons:

if time is between 11PM and 7 AM---3rd Shift
If time is between 7AM and 3 PM- Ist Shift
If time is between 3PM and 11 PM - 2nd Shift.

How do I do this?

 

[Debraj]

Hi Saminder,

Try this..

=LOOKUP($J2,{0,"3rd";0.291666666666667,"1st";0.625,"2nd";0.958333333333333,"3rd"}) & " Shift"

 

[Shaminder Singh]

Hi Debraj,

it does not work. Do I need to create some kind of lookup table?

thanks,
Shaminder

 

[Debraj]

Check the attached..
Is its giving correct result..

 

[SirJB7]

Hi, Shaminder Singh!

Debraj(ex-Roy)'s solution works perfectly. Give a look at the uploaded file.

There you have 2 solutions:

a) Column K: Debraj's formula

b) Column L: VLOOKUP function on a new table (Tabla4, columns N:O)
L2: =BUSCARV([Time];Tabla4;2;VERDADERO)&" Shift" -----> in english: =VLOOKUP([Time],Tabla4,2,TRUE)&" Shift"

For a) the only point with which you have to take care of is with the column and row separator characters used in your Excel version.
Debraj(ex-Roy) posted the English version: "," for columns and ";" for rows".
In my Spanish version (which you won't see unless you have it, i.e., you'll always see your local separators) I have to use these: "\" for columns and ";" for rows, since "," is my decimal separator.

Just advise if any issue.

Regards!

 

[SirJB7]

@Debraj(ex-Roy)
Hi!
Ooops...! Didn't see you, buddy.
Regards!

 

[Shaminder Singh]

it works..would you be able to explain this formula to me?

Thanks a lot,
Shaminder

 

[SirJB7]

Hi, Shaminder Singh!

Both solutions are simple formulas, have you tried using the built-in Excel help? It's very useful and almost sure that it'd provide you with the answers.

You could do this:
- take an empty cell
- type "=<function>(" unquoted and replacing "<function> by LOOKUP (1st solution) and VLOOKUP (2nd)
- click on "fx" (unquoted) symbol just at the left to the edit formula bar
... and you will be lead to the specific help for the function selected, where you'll find the description of how it works and examples of it use.

If you don't succeed, please tell us and we'll gladly explain you what does each formula does.

Regards!