Brief formula to convert time values to "shift1", "shift2", "shift3".

[darekpawel]

Hi - needed brief formula returning from time cells (ex. list below) text info "A", "B", "C" or "shift1", etc.
Where:
from 06:00:00 to 14:00:00 is "shift1"
from 14:00:01 to 22:00:00 is "shift2"
from 22:00:01 to 05:59:59 is "shift3"


08:30:22
04:57:57
11:11:55
04:54:45
11:33:44
04:48:57
21:26:46
12:52:33
13:46:20
13:21:55
12:28:38
05:21:27
10:48:07
05:17:20
04:59:49
00:53:19
21:06:47
23:10:59
20:48:31
20:24:48
16:35:28
12:18:21
20:56:30
13:18:11
12:33:45
12:43:15
05:18:53
07:18:55
05:19:01
20:27:18
17:02:08
19:26:45
21:05:22
13:31:11

[KOKOSEK]

If data starts in A1, put into A2:

Please Login or Register  to view this content.

and drag it down

[darekpawel]

Well... I'm doing something wrong?
Data starts in A1, and the returned I want in column B.

Attachment 619465

[Sam Capricci]

maybe this one...
=IF(AND(A1>=0.25,A1<=0.5833333),"shift 1",IF(AND(A1>=0.5833449,A1<0.9166667),"shift 2","shift 3"))
if you need to account for blanks then...
=IF(A1="","",IF(AND(A1>=0.25,A1<=0.5833333),"shift 1",IF(AND(A1>=0.5833449,A1<0.9166667),"shift 2","shift 3")))
change commas to semicolons if your settings require that.

[darekpawel]

Getting only "shift1"... Which is not true.

Attachment 619469

[KOKOSEK]

Sorry, of course if data starts in A1, it should be in B1 not A2.
Works for me. Are you sure that your data is date not text which looks like date?

Please Login or Register  to view this content.

[KOKOSEK]

=IF(AND(A1>=0.25,A1<=0.5833333),"shift 1",IF(AND(A1>=0.5833449,A1<0.9166667),"shift 2","shift 3"))
if you need to account for blanks then...
=IF(A1="","",IF(AND(A1>=0.25,A1<=0.5833333),"shift 1",IF(AND(A1>=0.5833449,A1<0.9166667),"shift 2","shift 3")))

Sambo how did You get this N169? :-)

[Sam Capricci]

FYI, I'm not sure if KOKOSEK can open your attachments but when I click on them I get an "invalid attachment" message. That is one item.
another thing, if you respond to someone it can be helpful that you either address the person you are replying to or note the post number (not sure if you were responding to me in post #5) but again, your attachment I cannot open (go advanced, then manage attachments etc. to upload a sample workbook if your issue isn't resolved).
So if neither are working maybe you have text instead of numbers (formatted to look like time).

[darekpawel]

Sorry, of course if data starts in A1, it should be in B1 not A2.
Works for me. Are you sure that your data is date not text which looks like date?

Please Login or Register  to view this content.

All cells are formatted as a "time"...


b734bfcc206b7f55med.jpg

[Sam Capricci]

Sambo how did You get this N169? :-)

LOL, didn't catch them all. That was the area of a test file where I plugged in the times. I tried to spot them all and change them to A1, guess I missed that one. (Only partway through my morning caffeine allotment.)

Edited it and made the correction. Maybe that is why it wasn't returning the correct value as requested.

[darekpawel]

I can open the attached "jpg's" on other PC's... Not sure why you can not open them...

Once again: the cells are formatted as "time" not as "date"...
Data uploaded from SAP - and it exports only like that example: "13:27:11".

[Sam Capricci]

I'm attaching my example for you, I corrected the one part in the formula that pointed at column N but otherwise it is as I posted it in post #4

[darekpawel]

Yes, in the example you attached it works... As I see..

But when I implement that in my xls - than this is not working...
Even if I format to "custom" = hh:mm:ss...
In all cells I see "shift3".




4.jpg

[torachan]

Are you Jurop (Italy).
Your default regional time separator is "." (point) not ":" (semi-colon).
Temporarily change your regional settings to UK or US and see if this is answer.
torachan.

[KOKOSEK]

Are you Jurop (Italy).
Your default regional time separator is "." (point) not ":" (semi-colon).
Temporarily change your regional settings to UK or US and see if this is answer.
torachan.

I don't think so.
If I am correctly read nick OP is Polish (as I am) and 'Jurop' is phonetic 'Europe' in Polish language.

[darekpawel]

It looks like exporting from SAP to xls is causing some hidden formatting in xls preventing proper formula calculation...
I do not have possibility to export from SAP the time data with other separator than “:”...

P.S. Yes, PL...

[torachan]

Does the export data contain a full date/time stamp.
if so is it the date element format that is the 'hidden' problem.
torachan.

[darekpawel]

I've looked into the problem.
The data exported from SAP to xls has one "space" added in front of each "time" series.
When I remove manually in xls the "space" - then the problem is solved.

On the other hand I have no idea why SAP is adding the 1 "space"...

[KOKOSEK]

So let's cut this leading space and convert it to number (date is a number):

Please Login or Register  to view this content.

[darekpawel]

Yes, this is solving that issue/problem.
Thank you everyone!

[KOKOSEK]

You welcome. (<PL ON>Nie ma sprawy </PL OFF>)
If you happy with solution please use thread tools and mark thread as SOLVED.