Using Times to make an IF statement

[hejman08]

I have a spreadsheet set up with Column B having a Start Time, Column C having a Duration, Column D having a calculated End time based on B and C. I then have 7 columns of "does this backup happen on this day of the week?" with Yes or No in the field. Then I have a 12:00am, 1:00am, ... 11:00pm Sunday and repeating through Saturday, one hour in each column, 24 hours for each Day of the week. What I want to do, using the example of Monday at 3am:
In the Monday at 3:00am column:
if the Monday cell is set to Yes for this row, AND 3:00am falls between the Start and End times for this backup, Display an X in the Monday 3:00am field, if not do nothing.
I have tried messing with TIME functions and If then statements (which I fully and freely admit that the more nested IF statements you have the more I suck at writing them) and just cannot make this work for me. Any suggestions would be appreciated.

~nathan

 

[hejman08]

Sorry, small correction.
I wrote

but I meant L3:FX5.

J.Ty.

This all looks very complicated and way over my head, so I'm glad I asked for help. The spreadsheet is located at my office so I will see if I can apply this all on Monday. Thank you so much for your help!

 

[J.Ty.]

It only looks complicated

OK, please do tests on Monday and let me know if everything is working as expected. Small corrections should be easy to incorporate, so do not hesitate to ask me.

J.Ty.

 

[hejman08]

It only looks complicated

OK, please do tests on Monday and let me know if everything is working as expected. Small corrections should be easy to incorporate, so do not hesitate to ask me.

J.Ty.

well, since I spent the better part of yesterday working on this before asking for your help, my vendetta against it has made me try it now, and it worked perfectly except in a few, specific places. On days that a backup which only takes a half an hour occurs (I didn't notice we had a few, smaller "logging" backups at the end of the sheet), a few of the columns get messed up for those backups, saying the backup occurs at times it doesn't and putting 3 x's in some of the time slots. I am not sure of the best way to actually post the problems on this board, but I would be more than happy to email the modified file that followed your instructions (and added 94 rows). The only ones that don't look pretty much perfect are the ones in which the backup only lasts 1/2 hour.

Let me know how best to get the file to you!
Thanks
Nathan

 

[J.Ty.]

Dear Nathan,

Copy here the values in columns B to K for those short backups. That will be enough.

J.Ty.

 

[hejman08]

Dear Nathan,

Copy here the values in columns B to K for those short backups. That will be enough.

J.Ty.

[TABLE="width: 650"]
<colgroup><col><col span="9"></colgroup><tbody>[TR]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]0:30[/TD]
[TD="align: right"]4:30 PM [/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]0:30[/TD]
[TD="align: right"]4:30 PM[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]0:30[/TD]
[TD="align: right"]4:30 PM[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

Those are 3 of them that end up having 3 X's in column AC and other days at that time (Sunday 4pm)

 

[J.Ty.]

Replace >= by > in formulas in cells D18 and D19.

That removes the anomaly.

J.Ty.

 

[J.Ty.]

Dear Nathan,

Can you contact me, please?

J.Ty.