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 |
+-------------------------------------------------------------------+

--
magecca
------------------------------------------------------------------------
magecca's Profile: http://www.excelforum.com/member.php...o&userid=29250
View this thread: http://www.excelforum.com/showthread...hreadid=489734

=IF(OR($D3G$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" wrote in
message ...

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 |
+-------------------------------------------------------------------+

--
magecca
------------------------------------------------------------------------
magecca's Profile:
http://www.excelforum.com/member.php...o&userid=29250
View this thread: http://www.excelforum.com/showthread...hreadid=489734

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

=IF(OR($D3G$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" wrote in
message ...

Bob Phillips Wrote:
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.


--
magecca
------------------------------------------------------------------------
magecca's Profile:
http://www.excelforum.com/member.php...o&userid=29250
View this thread: http://www.excelforum.com/showthread...hreadid=489734

One more shot

=IF(AND($D3G$2,$D3<G$2+1/24,$F3G$2),"-X",
IF(AND($D3<G$2,$F3G$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" wrote in
message ...

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 Wrote:
I think this is due to floating point arithmetic. I can get around it,
don't
like it, but I can, with


=IF(OR($D3G$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?



--
magecca
------------------------------------------------------------------------
magecca's Profile:
http://www.excelforum.com/member.php...o&userid=29250
View this thread: http://www.excelforum.com/showthread...hreadid=489734

Thank you! That's what I am looking for. Now I have to try to understand
it [o: hehe. Thank you again!
Bob Phillips Wrote:
One more shot
=IF(AND($D3G$2,$D3<G$2+1/24,$F3G$2),"-X",
IF(AND($D3<G$2,$F3G$2,$F3<G$2+1/24),"X-",
IF(AND($D3<=G$2,$F3=G$2),"XX","--")))



--
magecca
------------------------------------------------------------------------
magecca's Profile: http://www.excelforum.com/member.php...o&userid=29250
View this thread: http://www.excelforum.com/showthread...hreadid=489734

Great, I am glad we finally made it :-))

Bob


"magecca" wrote in
message ...

Thank you! That's what I am looking for. Now I have to try to understand
it [o: hehe. Thank you again!
Bob Phillips Wrote:
One more shot
=IF(AND($D3G$2,$D3<G$2+1/24,$F3G$2),"-X",
IF(AND($D3<G$2,$F3G$2,$F3<G$2+1/24),"X-",
IF(AND($D3<=G$2,$F3=G$2),"XX","--")))



--
magecca
------------------------------------------------------------------------
magecca's Profile:
http://www.excelforum.com/member.php...o&userid=29250
View this thread: http://www.excelforum.com/showthread...hreadid=489734