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:

Please Login or Register  to view this content.

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

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[magecca]

Please Login or Register  to view this content.

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!

[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" <[email protected]> wrote in
message news:[email protected]...
>
> Bob Phillips Wrote:
> > >
> Code:
> --------------------
> > >
=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-")))
> --------------------
> > >
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> magecca
> ------------------------------------------------------------------------
> magecca's Profile:
http://www.excelforum.com/member.php...o&userid=29250
> View this thread: http://www.excelforum.com/showthread...hreadid=489734
>

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

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?

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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($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?
> >
>
>
> --
> magecca
> ------------------------------------------------------------------------
> magecca's Profile:
http://www.excelforum.com/member.php...o&userid=29250
> View this thread: http://www.excelforum.com/showthread...hreadid=489734
>

[magecca]

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","--")))

[Bob Phillips]

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

Bob


"magecca" <[email protected]> wrote in
message news:[email protected]...
>
> 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($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","--")))
> >
>
>
> --
> magecca
> ------------------------------------------------------------------------
> magecca's Profile:
http://www.excelforum.com/member.php...o&userid=29250
> View this thread: http://www.excelforum.com/showthread...hreadid=489734
>