Nested IF Statement using Time Variables 2

[ladyck3]

HELP....
This is the statement I concocted and I know its screwed up big time but let me paste it so I don't lose it:

=IF(d67<=B2,0,IF(d67(<b2+3),0.25),if(b67>(b2+3)<(b2+15),0.05,if(b67>(b2+15),1.0))))

B2 = 8:00:00 (login scheduled time)
B67 = 8:02:35 (actual login time)

I need to calculate a point system with the following criteria.

If the actual login is

Earlier or = to 8:00 then enter the value 0
If it is > 8:00:00 but less than 3 minutes 0.25
If it is > 3 minutes but less than 15 0.50
If it is > 15 minutes 1

HELP...
First question, do I have to have a separate calculation sheet to convert the time to seconds? I have one already, I can just add this criteria.

If I am doing this I guess I can link to the seconds and refer to the calculation sheet but I'm new to nested IF Statements and the kink for me is the >3<15 part.

HELP! I'm sure my initial formula above is total junk but I tried

Thanks!
Learning in Texas


LadyCK3
aka: Laurie

 

[ladyck3]

FINAL REPORT.... SUCCESS - Please see comments.

kwbMitel, thanks SO MUCH for the extra work and the excellent break down.. I am going to save this in my lil bag 'o tricks for future use if needed... I do appreciate all of your time.

Combo, thank you too... it was a winner, seriously for what YOU completed but when I tried to change it on my own, for log OUT time, well woah's me But I'm still thankful that from the get-go, you made it understandable, I GOT IT (what was completed)

Tony, sorry for confusing you about dates and times... but you can't imagine MY confusion... well LOL, if you have been following the thread (anyone) I bet you FELT my confusion HAHAHHAHA

xlhelp - I think you ran early on and I have to give you HIGH FIVES for that.... I would have run too... having to deal with such stupidity (with a huge eagerness to LEARN)

BUT... last and NOT AT ALL LEAST, but more like the MOSTEST!!

HUGS TO SKIP (and stars of course)
Oh man, thanks so much for completely sticking with me and for working so hard while I was knocking my head against brick so STAY WITH ME! You are ALWAYS there... I mean it, is this your job? If its not, my apologies to your employer for taking up ALL of your day yesterday.

Guys, learn what he taught me, its AWESOME and SO EASY.

I have 4 schedules to deal with 4 login times, 8:00, 8:15, 8:30 and 9:00 and two log out times 5:00 and 6:00.

I created 4 tables, TIM VAL, TM2, VL2, etc. I had to put an UNDERSCORE after the headers with numbers but I figured that out and yes.. ON MY OWN!

I couldn't stand it, I could not wait until today to work with it so I worked about 3 hrs last night to get ALL of my worksheets (one for each agent) set up and its all working MARVELOUSLY....

Skip, you are pricelessly valuable. *** I *** APPRECIATE YOUUUUUUUUUUUU! Thank you!


LadyCK3
aka: Laurie

 

[xlhelp]

Laurie. I didn't run. It's just pressures of work that don't give me enough time to work through a solution.

I am glad you got this sorted out. The nice thing about these forums (fora) is that someone else can pick up where one left off.

When you have giants helping you out, you really don't need a midget (in comparison) like me.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[ladyck3]

Oh I meant no ill withe the 'run' comment... I totally understand about work pressures... THIS was mine

You are absolutely accurate... this is the best resource on the web for interactive, right there, spot on guidance.... assistance, help. And youare no midget... I see you helping out ALL the time

Have a great weekend everyone!

LadyCK3
aka: Laurie

 

[ladyck3]

Having a problem with this table, but just one of them.
(Skip's solution is what I'm referring to)

I have 5 tables that I use....
EXAMPLE OF THE TABLE:
8:00 to 5:00 (table 1)
------------------------------
TIM VAL
0:00:00 0
8:00:00 0.25
8:03:00 0.5
8:15:00 1
16:45:00 0.5
16:57:00 0.25
17:00:00 0
---------------------------------
OTHER TABLES
---------------------------------
8:15 to 5:00 (table 2)
8:30 to 5:00 (table 3)
9:00 to 6:00 (table 4)
8:30 to 5:30 (table 5)

----------------------
TABLE 4 NOT WORKING
----------------------
The one I am having a problem with is #4, times less than 3 minutes over are showing up with a full point.

TABLE 4

TM4 VL4
0:00:00 0
9:00:00 0.25
9:03:00 0.5
9:15:00 1
17:45:00 0.5
17:57:00 0.25
18:00:00 0

Now why is this not working? As I see it, its the same as the rest, right?

The forumula being used:

=INDEX(VL4_,MATCH(D237,TM4_,1),1)

These table are identical in creation, identical in all ways except for the time increments.

Results:
9:02:41 1.00 Should be 0.25
9:00:35 1.00 Should be 0.25
9:03:53 1.00 Should be 0.50
9:04:07 1.00 Should be 0.50
9:01:04 1.00 Should be 0.25

Its like in reverse but how?

Thanks all (Skip)




LadyCK3
aka: Laurie

 

[SkipVought]

You may have to change a time value by ONE SECOND one way or another, to meet your criteria.

Skip,

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

 

[ladyck3]

Ya know what I did? It just made no sense, so I changed the column titles from TM4 to T4M... with a number at the end (which all of the others have) the system puts an underscore after it. For some reason, I thought, maybe its having an issue with that ONE table.

I renamed TM4 as above and VL4 to V4L and there ya go... it all snapped into place.

Go figure.

See I'm not totally ignorant

HA HA HA Thanks Skip


LadyCK3
aka: Laurie

 

[SkipVought]

FYI,

Check out the Name Box DropDown, paying particular attention to UNDERSCORE characters.

And if your're using 2007, TM4 happens to be a valid cell referece.

Skip,

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

 

[ladyck3]

How come, when I named them TIM, TM1, TM2, TM3, TM4 and TM5, all of those with numbers were converted BY 2007 to read TM1_, TM2_, etc?

The workbooks were saved as 2007 file types, they were not first saved as xls and 'saved as' xlsx

I created them as 2007 spreadsheets so there was no compatibility issue thing.

Oh whatever, maybe it was some sort of corruption thing or something... all I know is when I changed the headings on that one table, that table snapped in and is working fine... I'm cool with it


LadyCK3
aka: Laurie

 

[SkipVought]

Same reason. Name TM1 is a cell reference. (Enter TM1 into the Name Box and see where it takes you.) So When you Name Ranges using Create Names, Excel appends an UNDERSCORE.

Skip,

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