Basic question

[firecritter16]

I apologize in advance because I am sure this is a terribly basic question, but if there is a solution it will save me a lot of headaches.

I manage a vet hospital and I use excel to create our employee schedule. An example of an employee listing on the schedule is: Rhea 7:30-6.

What I would love is if I could make the phrase 'Rhea 7:30-6' equal 10 (hours) so that I can just have it calculate each person's total hours at the end of the week on the excel spreadsheet. Everyone's schedules change regularly, so I end up doing a lot of calculating.

I basically make the spreadsheet into a calendar, one month per page. When you open a page it has the month listed at the top and then looks just like a calendar page. What I want is to set a formula in the cells at the end of each week that can total each person's hours but leave the 'phrase' still showing.

I hope I made that clear enough and I hope one of you incredible people has the answer. Thank you in advance.

 

[JohnnyTightlips]

If I understand what you're trying to do correctly, it's not that easy to do. There are some brilliant people on this forum, so there will be a solution but it will be a pretty complex formula I assume.

If working hours are Standard, you may be able to create a table listing the standard shift times and match them against what is entered in the cell by taking advantage of wildcard charcaters available in VLOOKUP formulas and the like

Hope a solution is found for you

 

[acw]

Hi

One way would be to build your own function to do this.

You will need to have a list of the "Phrases" and the related hours set up in a table fashion.

Say you have Rhea: 7:30-6,10 in the range K3:L3 and your data in the range C3:G3 the using then something like

=myfunc(C3:G3,K3:L3,"Rhea")

Function myfunc(ra As Range, lu As Range, na As String)
  For Each ce In ra
    If Left(ce, Len(na)) = na Then holder = holder + WorksheetFunction.VLookup(ce, lu, 2, False)
  Next ce
  myfunc = holder
End Function

Put the function in a general module in the relevant spreadsheet and see if that will work for you.


Tony

 

[firecritter16]

Thank you to those who have helped so far. Please let me modify my initial question a bit.

I should have used the word beginner in capital letters as a description of myself in my initial post. I did not assume this would be a terribly complicated thing to do, so I guess I left that out.

If this is beyond what I can be taught without taking a class, that is fine- I was a psych major for my initial degree and then a vet tech, so computer stuff was beyond my scope.

I have another thought in my head, if it is possible and not terribly complicated. What if I calculated the hours myself for each day- is there a way to give a cell a value, but have something other than that value visible? Say I could give the cell 'Rhea 7:30-6' a value of 10, can I still have 'Rhea 7:30-6' show in the cell instead of 10?

Also if a cell has a fill color, does excel recognize this color? If the above was possible and all Rhea cells are colored green, can excel recognize them as the the same and add the green values together?

I wish that I knew a bit more about this so that my questions would not be so confusing. Thank you all for your time and patience.

 

[Smitty]

If you're truly a "newb", then I'd focus on Excel's core native capabilities, instead of yes, what it can do, but for those who are not "newbs".

I.E. Using four columns: Name, Time In, Time Out and Total will allow you to utilize Excel much better and be able to use simple functions as opposed to complex ones that may or may not be as flexible as you. Trying to concatenate Text and numbers in a cell and perform calculations, especially with time can be tricky at best.

As for counting colored cells, you need to use VBA. There is a site that explains it clearly, but it requires VBA (the programming end of Excel, so I don't think from your description that's in your comfort range, but if it is, let me know and I'll post the link.

Hope that helps

Smitty

Over all else try to keep it simple!