Time formula, complicated?

[magecca]

I'm trying to make a simple spreadsheat, in which i type in my work
start and end time, then I have some cells that have time hours
(07:00-21:00) at each cell it calculates whether it's time IE: 07:00
falls between the time entered, if it does it puts "XX" if not "--"

the forula I use:
Code:
--------------------
=IF(G$2>=$D3,IF(G$2<=$F3, "XX", "--"), "--")
--------------------

And it works, so far but I want each "X" or "-" to represent thirty
minutes, I just can't figure out the formula to tell if it is A the
beginning of the shift and it begins at .5 make the mark be "-X" or if
B it's the end of the shift and it ends at .5 make the mark "X-"

See the image attached. Much obliged for any help you may provide
-Thanks


+-------------------------------------------------------------------+
|Filename: Shedule_Simple.png |
|Download: http://www.excelforum.com/attachment.php?postid=4086 |
+-------------------------------------------------------------------+

 

[Bob Phillips]

=IF(OR($D3>G$2+1/48,$F3<G$2-1/48),"--",IF(AND($D3<=G$2,$F3>=G$2),"XX",IF($D3
=G$2+1/48,"-X","X-")))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[magecca]

Code:
--------------------

THANK YOU so much! It's wonderful but still slightly off and again, I
can't fingure it out. I figured it needed some tweaking but it just
isn't working. See the attachment for illustration. Thank you again for
your help previously. Do you understand pertty much what I want? Again,
thank you!


+-------------------------------------------------------------------+
|Filename: Shedule_Simple_a.png |
|Download: http://www.excelforum.com/attachment.php?postid=4089 |
+-------------------------------------------------------------------+

 

[Bob Phillips]

It works exactly as you indicate that you want in my tests. What do you see,
and please tell me, not an image, I don't access this through ExcelForum.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[magecca]

It works exactly as you indicate that you want in my tests. What do you
see,
and please tell me, not an image, I don't access this through
ExcelForum.

For example if the -Start- was *10:30* under the "*10*" column i get
the appropriate "-X" (thank you) and if the -End- time is *20:30* all
the colums are correct until the "*20*" column where is has the
incorrect "XX" and the "21" column as the "X-" when the "*20*" colum is
the one which should have that value ("X-"), depecting half of the 20th
hour.

Forgive me if this is rather difficult to explain. I can not thank you
enough for your help.

 

[Bob Phillips]

I think this is due to floating point arithmetic. I can get around it, don't
like it, but I can, with

=IF(OR($D3>G$2+1/48,ROUND($F3+1/48,6)<=ROUND(G$2,6)),"--",IF(AND($D3<=G$2,RO
UND($F3,6)>ROUND(G$2+1/48,6)),"XX",IF($D3=G$2+1/48,"-X","X-")))

Let us know if that sorts it.

Do you want a formula to count the X's and convert to hours worksed?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[magecca]

Mr. Phillips,
Now no matter if the hour is whole or half it puts the half mark "X-"
at the end time. Should I attach the spreadsheet? Maybe "Floor" or
"Ceil" if they are availabe instead of "Round"? Sure, I may be able to
use that formula, thank you. Perhaps maybe split up the whole and
halves to it's own column but that will make my spreadsheet way larger
than I intended.
Thanks for all your time -Matt

 

[Bob Phillips]

One more shot

=IF(AND($D3>G$2,$D3<G$2+1/24,$F3>G$2),"-X",
IF(AND($D3<G$2,$F3>G$2,$F3<G$2+1/24),"X-",
IF(AND($D3<=G$2,$F3>=G$2),"XX","--")))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[magecca]

Thank you! That's what I am looking for. Now I have to try to understand
it [o: hehe. Thank you again!

 

[Bob Phillips]

Great, I am glad we finally made it )

Bob

Thank you! That's what I am looking for. Now I have to try to understand
it [o: hehe. Thank you again!

One more shot
=IF(AND($D3>G$2,$D3<G$2+1/24,$F3>G$2),"-X",
IF(AND($D3<G$2,$F3>G$2,$F3<G$2+1/24),"X-",
IF(AND($D3<=G$2,$F3>=G$2),"XX","--")))