Trying to create an automated Duty Scheduler with Calc

[shadmanfx]

To give you an idea of what I am trying to do.

[COVERAGE] inputed each week

on input of a day in selected cell, start automatic inputs of scheduled times in another box
and also testing each possibility of coverage per person per hour of coverage for duties

such as input = monday

jimmy - 12:30 (in) 15:00 ( lunch) 16:00 19:00 (out)
helen - 14:30 (in) 16:30 ( lunch) 17:30 22:00 (out)

taking information like this and running tests on them to decide which associate will be put for that duty ... testing the duty times to the associates coverage times.

[shadmanfx]

^^bump^^ guess this isnt the place to ask.

[Villeroy]

[shadmanfx]

good point i forgot to ask..

I have everything right now to put the data in the appropriate cells, except the VANNA section... im trying to create a formula to pick the best associate to do that duty.

I want the spreadsheet to pick someone whos schedule time doesnt conflict with the duty time... which is 1 hour. If Trevor works at 11:00 on monday and has lunch at 3 he can do vanna between 11-3 if his other duties dont conflict. such as cleaning the bathrooms safety sweep or cart run.

usually the associates do bathroom checkss and cart run during the same hour

safety sweep and vanna both take an hour each so they need to be different hours.

so Trevor can do vanna at 11:00 safety sweep at 12:00 and cart run / bathrooms at 1:00

If jimmy works at 11:30 to 15:30
he could do safety sweep at 1:00 - cart run at 2:00 - vanna at 12:00
etc etc

the formula would make the duty list easier based on coverage, work hours, lunch times etc.

so far i am trying this formula.

=IF(x=q;y;IF(x=w;u;IF(x=e;i;IF())))))

etc

but im trying to figure a formula to take data and some what randomize a pick to go off that formula above


this is some what confusing and hard to explain. Any information or input is greatly appreciated. Thank you.

[shadmanfx]

right now im trying to take the schedule times and make duties...


the formula im using for the duties at 10AM
=IF(E2<=L2;A2;IF(E3<=L2;A3;IF(E4<=L2;A4;IF(E5<=L2;A5;IF(E6<=L2;A6;IF(E7<=L2;A7;IF(E8<=L2;A8;IF(E9<=L2;A9))))))))

this works... the only thing is... i need to incorporate another formula to return an error ... example, if an associate has vanna duty and safety sweep duty at 10AM ... that cant happen....

[Villeroy]

So all you ask for is:
"I have absolutely no experience with spreadsheets and want somebody to do a totally overambitious project for me".
IF is not the solution. IF is the problem here. Forget the IF function.

Spreadsheets are a huge misunderstanding. Very, very experienced users may be able to use a spreadsheet for what you intend to do but spreadsheet never have been desinged to do this.
This task is what databases are designed for. Databases keep interrelated lists in a most convenient and consistent manner and return row sets rather than calculation results. Databases derive tables from other tables. Spreadsheets calculate figures from other figures.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[shadmanfx]

i understand this doesnt seem logical as spreadsheets tend to be more basic.. But if you have any suggestions towards helping me figure out "A WAY" to create a formula for this spreadsheet to do what i said i was trying that would be great...

I have no other programs to use.. this is the only one that my company will support. So ill try and come up with something to make this happen in calc.

[Villeroy]

Sorry, we can not teach you how to use a spreadsheet, particular when you are in need of a database actually.
There are thousands of unspecific requests asking for help starting at point zero.
Just answered the same on the official forum: http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=54366
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org