Time and date formula problems

Good Day,

I am having issues with time formulas and been struggling for two days now. I am good at excel formulas just not time.

1) subtracting time and dates in the same field and displaying as text. It is the text part that is kicking my butt, I tried this

A1 B1 c1
3/3/2014 23:32:00 3/3/2014 23:33:58 =24*(b1-A1) but it gives me the wrong answer :-(


The next I am having issues with is because I can not get the time displayed as text correctly.

2) I need to pick out ones in C1 above that is less than 15, >15<30, >30<45, >45<1:00, etc in different columns. The <15 is easy it is the ones that are in between 2 times that i can't get (why I am trying to make the times text)

I am so stuck it is bad and I am starting to repeat my failures any help or push in the correct direction is greatly appreciated.

One day I want to understand time and dates in excel. I read and think I understand then this happens :-(.

Thank you,

AlKey,

Thank you for your response and I have tried that and the issue is that method still leaves it in time format. This creates a problem when it is over 24 hours. The display is correct but it is still in AM/PM format which causes problems down the road.

I think I have solved the time to text issue with =TEXT(LEFT(S2,5),"hh:mm:ss") But still can not break out the data to increments of 0 to 15 min, 15 to 30 min, 30 to 45 min, 45 to 1hr, over 1 hr, over 5 hr, over 10 hr and so on.

I have attached a spreadsheet to try and give better examples.

Example.xlsx

Thank you,

I am sorry here is an example that has the formulas that have gotten me this far.

Thanks

Example.xlsx

AlKey and alvin-chung,

Thank both of you you guys are great and not only solved my problem but you taught me something I will use in the future. I have always said this is the greatest excel forum and it all because of people like the two of you.

Thank you again

Hi DABlaylock, you're welcome and thanks for the reputation


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin