Basic question

firecritter16

New Member
Joined
Oct 9, 2006
Messages
2
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. :biggrin:
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

JohnnyTightlips

Board Regular
Joined
Aug 13, 2006
Messages
94
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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")

Code:
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

New Member
Joined
Oct 9, 2006
Messages
2
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

Legend
Joined
May 15, 2003
Messages
29,536
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!
 

Forum statistics

Threads
1,141,924
Messages
5,709,378
Members
421,633
Latest member
Ubergribbler

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top