## Resource Allocation (personnel) Excel Add-on. |

## Resource Allocation (personnel) Excel Add-on. - Excel |
View Answers |

Hello,

I am wondering if there is an Excel Add-on (Plugin) which would allow me to do some resource/staff management. I am looking for something that would let me do the following:

1. Able to allocate personnel to several projects. For example 1 person might split half of its time to one project and the other half to a second project.

2. Keep track of time worked by each person on each project

3. Easily scalable to 5-10 projects.

Thanks

I am wondering if there is an Excel Add-on (Plugin) which would allow me to do some resource/staff management. I am looking for something that would let me do the following:

1. Able to allocate personnel to several projects. For example 1 person might split half of its time to one project and the other half to a second project.

2. Keep track of time worked by each person on each project

3. Easily scalable to 5-10 projects.

Thanks

## Free Excel Help Forum

**- Ask any question about Excel and have it answered in no time.**

## Subscribe for Weekly Tutorials

### Helpful tutorials delivered to your email!

## Helpful Excel Macros

Delete a VBA Module From Excel

- Delete a VBA macro module from Excel with this macro. This macro allows you to fully remove a macro module from Excel.

- Delete a VBA macro module from Excel with this macro. This macro allows you to fully remove a macro module from Excel.

Format Cells as Time in Excel

- This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst

- This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst

Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day

- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da

- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da

Create a Bar Chart With a Macro in Excel

- Create a bar chart in Excel with this macro. You will be able to quickly and easily turn any range of numbers and data

- Create a bar chart in Excel with this macro. You will be able to quickly and easily turn any range of numbers and data

Name Worksheets Based on Cell Contents

- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means

- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means

## Similar Topics

Hi,

I made a simple worksheet that we have been manually entering the time for employees, but there are too many errors (even with simple math). Can someone help me convert the time of ex: written 8-4:30 or 10-3 (meaning 8:00am to 4:30pm) where you have the total weekly hours? Right now I have a column for overtime. Is there a way to automatically calculate the over time also? The work day is 8-4:30 with a half hour lunch (lunch is not calculated into the hours, so you minus a half hour). 8-4:30 equals 8 hours. So, if a person works 8am to 6:30pm, how can I set it up where in the first row of time, I will type in 8-6:30pm. In the totals column to the far right, it will display 8 hours. In the (O.T.) column, it will display 2 hours.

This way, when I call in my payroll, I will have the total "regular" hours in one row and underneath, I will have the total overtime hours.

Or... does anyone have a better solution to keeping track of their hours?

Thanks!

Marty

I made a simple worksheet that we have been manually entering the time for employees, but there are too many errors (even with simple math). Can someone help me convert the time of ex: written 8-4:30 or 10-3 (meaning 8:00am to 4:30pm) where you have the total weekly hours? Right now I have a column for overtime. Is there a way to automatically calculate the over time also? The work day is 8-4:30 with a half hour lunch (lunch is not calculated into the hours, so you minus a half hour). 8-4:30 equals 8 hours. So, if a person works 8am to 6:30pm, how can I set it up where in the first row of time, I will type in 8-6:30pm. In the totals column to the far right, it will display 8 hours. In the (O.T.) column, it will display 2 hours.

This way, when I call in my payroll, I will have the total "regular" hours in one row and underneath, I will have the total overtime hours.

Or... does anyone have a better solution to keeping track of their hours?

Thanks!

Marty

Hi all,

I have a dillemma here,

I have to create a schedule which incorporates lunches automatically (either half hour or 1-hour lunches, depending on the circumstances).

The schedule only has time in and time out, but I need excel to automatically deduct the lunch break depending on the circumstance:

If you worked less than 6 hours = no lunch

If you worked more than 6 hours, but less than or equal to 8 hours = 0.5 hour lunch

If you worked more than 8 hours = 1 hour lunch

I am assuming the formula for this will be really long, but I have looked online everywhere and have not found ONE formula for it. I can't put lunch breaks seperately, so all I have to work with is Time in/out.

Also, I wouldn't be able to use military time, so I don't know how excel can assume that time in is in the morning, and time out is in teh evening and/or half-day.

If someone can help it would be greatlyyyy appreciated

I have a dillemma here,

I have to create a schedule which incorporates lunches automatically (either half hour or 1-hour lunches, depending on the circumstances).

The schedule only has time in and time out, but I need excel to automatically deduct the lunch break depending on the circumstance:

If you worked less than 6 hours = no lunch

If you worked more than 6 hours, but less than or equal to 8 hours = 0.5 hour lunch

If you worked more than 8 hours = 1 hour lunch

I am assuming the formula for this will be really long, but I have looked online everywhere and have not found ONE formula for it. I can't put lunch breaks seperately, so all I have to work with is Time in/out.

Also, I wouldn't be able to use military time, so I don't know how excel can assume that time in is in the morning, and time out is in teh evening and/or half-day.

If someone can help it would be greatlyyyy appreciated

I have created an excel spreadsheet to track attendance of an exercise class I am teaching. I would like to know each person's current percent attendance. (so if we have had 5 classes and they have attended 4 of those classes then it should show 80%). I would like it to keep a running tab, so as I add in the person's attendace it will continue that count, up to the 26 classes that are being offered. I thought if I did a sumif/countif I could get it to work, but it doesn't. Here is what I tried: =sumif($e$4:$ad:4,">0")/countif($e$4:$ad$4,">1"). Do you have a suggestion that would work? Thanks!

We have an Excel spreadsheet that sits on the network.

People need to open the file to be able to sign up for various duty rosters.

We would like for the file to open for the first person.

And then for any others after that, get a message that the file is in use

WITHOUT the option to open a read-only copy.

Our staff can't read and they keep opening additional copies of the file!

I have read about sharing the file and I don't think that would make things

any better.

Thanks,

Lynn

People need to open the file to be able to sign up for various duty rosters.

We would like for the file to open for the first person.

And then for any others after that, get a message that the file is in use

WITHOUT the option to open a read-only copy.

Our staff can't read and they keep opening additional copies of the file!

I have read about sharing the file and I don't think that would make things

any better.

Thanks,

Lynn

Hello,

Please help!!!

We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:

From the hour mark to 6 minutes = 0 time paid

Over 7 minutes to 15 minutes = 1/4 hour 15

16 minutes to 30 minutes = 1/2 hour 30

30 minutes to 45 minutes = 3/4 hour 45

46 minutes to 60 minutes = one hour.

They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):

WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday

5/16 5/17 5/18 5/19 5/20 5/21 5/22

TIME IN ENTER TIME, INCLUDE AM OR PM

TIME OUT

TIME IN

TIME OUT

TIME IN

TIME OUT

TIME IN Total Hours First Week

TIME OUT

HOURS WORKED-OR- choose one

*HOLIDAY WORKED

PAID HOLIDAY (not worked)

*OTHER PAID HOURS

EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER

I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.

Thanks..

Please help!!!

We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:

From the hour mark to 6 minutes = 0 time paid

Over 7 minutes to 15 minutes = 1/4 hour 15

16 minutes to 30 minutes = 1/2 hour 30

30 minutes to 45 minutes = 3/4 hour 45

46 minutes to 60 minutes = one hour.

They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):

WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday

5/16 5/17 5/18 5/19 5/20 5/21 5/22

TIME IN ENTER TIME, INCLUDE AM OR PM

TIME OUT

TIME IN

TIME OUT

TIME IN

TIME OUT

TIME IN Total Hours First Week

TIME OUT

HOURS WORKED-OR- choose one

*HOLIDAY WORKED

PAID HOLIDAY (not worked)

*OTHER PAID HOURS

EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER

I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.

Thanks..

I'm trying to have separate column widths in the same column, based on the rows.

the top half of my spreadsheet are assumptions, formatted with the corrects widths needed, but then below I have calculations and such that I do not want the same widths for. how can I lock in the top part so I can decrease the column widths for the bottom half of my spreadsheet?

hope this makes sense.

the top half of my spreadsheet are assumptions, formatted with the corrects widths needed, but then below I have calculations and such that I do not want the same widths for. how can I lock in the top part so I can decrease the column widths for the bottom half of my spreadsheet?

hope this makes sense.

I have a sheet which has the start, stop and time taken for lunch breaks.

I want to calculate the total time worked such that i have the hours worked. So if i worked 7 hours and 30min then the total time would be 7.5hrs. But i am not sure how to take into account the lunch break time (see enclosed sheet).

anyone have any ideas?

I want to calculate the total time worked such that i have the hours worked. So if i worked 7 hours and 30min then the total time would be 7.5hrs. But i am not sure how to take into account the lunch break time (see enclosed sheet).

anyone have any ideas?

Arcangelo from Italy asks: How can I write an Excel VBA macro to save the current Excel file with a filename derived from cell A1?

This macro is amazingly straight-forward:

Public Sub SaveAsA1()

ThisFile = Range("A1").Value

ActiveWorkbook.SaveAs Filename:=ThisFile

End Sub

I'm totally lost on this "amazingly straight-forward" macro!!!! Could someone help if I tell you the SAVE location? It's T:\COMMISSIONING\IJT\TIMELOG project\Staff#1. I'm just not certain what value I'm replacing in the Macro above.

Any help appreciated..

This macro is amazingly straight-forward:

Public Sub SaveAsA1()

ThisFile = Range("A1").Value

ActiveWorkbook.SaveAs Filename:=ThisFile

End Sub

I'm totally lost on this "amazingly straight-forward" macro!!!! Could someone help if I tell you the SAVE location? It's T:\COMMISSIONING\IJT\TIMELOG project\Staff#1. I'm just not certain what value I'm replacing in the Macro above.

Any help appreciated..

Sorry for the question. Normally I find answers to my excel questions by going through the help tab or by searching on Google. However, I don't even know what question to ask on this one!?!

Basically I have created a spreadsheet with several columns, but I have one column that lists the shirt size (YS, YM, YL, AS, AM, AL, XL, 2X, 3X) of each person. Is there a formula that I can create that will tabulate the number of sizes (i.e. AS=2, AM=7, etc.)?

In previous years I made a column for each size, and simply placed a "1" in the correct column, and had excel just add the 1's from each column. However, that takes more time and space. I was hoping to streamline it this time around.

Thanks for taking the time to read this post. Any help would be appreciated! Thanks, doug

Basically I have created a spreadsheet with several columns, but I have one column that lists the shirt size (YS, YM, YL, AS, AM, AL, XL, 2X, 3X) of each person. Is there a formula that I can create that will tabulate the number of sizes (i.e. AS=2, AM=7, etc.)?

In previous years I made a column for each size, and simply placed a "1" in the correct column, and had excel just add the 1's from each column. However, that takes more time and space. I was hoping to streamline it this time around.

Thanks for taking the time to read this post. Any help would be appreciated! Thanks, doug

I am attempting to figure out how to change the formula to automatically subtract a 30 minute lunch if the total time for the day exceeds 6 hours worked.

B value Time of day punch in: 9:30 AM

C value =IF(D11="","","-")

D value Time of Day punch out: 6:00 PM

E value =IF(D11="","",IF(D11

B value Time of day punch in: 9:30 AM

C value =IF(D11="","","-")

D value Time of Day punch out: 6:00 PM

E value =IF(D11="","",IF(D11

I'm trying to use conditional formatting to highlight phone calls that came in between certain hours.

The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames.

Example: If the call came in between 22:00 and 23:59 color is light green.

If the call came in between 23:59 and 08:00 the color is yellow.

Example spreadsheet is attached.

Thanks in advance.

The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames.

Example: If the call came in between 22:00 and 23:59 color is light green.

If the call came in between 23:59 and 08:00 the color is yellow.

Example spreadsheet is attached.

Thanks in advance.

I have a Workbook with 2 sheets, the first one is the data entry and the second one contains all the calculations and confidential info.

I have one staff member that does the data entry but I don't want them to see the 2nd sheet. I know I can hide the sheet then protect the entire workbook which does work, but is there an easier way so that the second person doesn't have to keep hiding/unhiding the 2nd sheet?

It would be perfect if when you tried to click on Sheet2, it asked for a password.

Cheers

Jase

I have one staff member that does the data entry but I don't want them to see the 2nd sheet. I know I can hide the sheet then protect the entire workbook which does work, but is there an easier way so that the second person doesn't have to keep hiding/unhiding the 2nd sheet?

It would be perfect if when you tried to click on Sheet2, it asked for a password.

Cheers

Jase

Hello,

I'm pretty new at VBA and was wondering if you could help me out on this:

I have created a VBA userform but will need to have it used by at least 5 users. My question is, can it be done with all users working at the same time and when saving their work all data entered will go to one master excel sheet? if yes, would you be kind enough to share the code?

MS Access is not an option for me so I was wondering if you could help me do this in excel.

thank you so much and would really appreciate to hear from anyone soon.

I'm pretty new at VBA and was wondering if you could help me out on this:

I have created a VBA userform but will need to have it used by at least 5 users. My question is, can it be done with all users working at the same time and when saving their work all data entered will go to one master excel sheet? if yes, would you be kind enough to share the code?

MS Access is not an option for me so I was wondering if you could help me do this in excel.

thank you so much and would really appreciate to hear from anyone soon.

In Excel I have been trying to find an easier way to calculate a time

difference where the times cross midnight. Example:

Start time: 23:50:00

End time: 00:15:00

How would you formulate an equation to determine the duration of time or

differnce between the start and end time?

difference where the times cross midnight. Example:

Start time: 23:50:00

End time: 00:15:00

How would you formulate an equation to determine the duration of time or

differnce between the start and end time?

I have an excal project that requires me to use degrees, minutes and seconds.

How do I enter this into a cell

How do I enter this into a cell

Happy Day to all,

Can you please help me,

A1= time in

B1= time out

C1= time in

D1= time out

I want to calculate the late and under time,

Office start at 9:am w/30 mins Grace period,

The break time is one hour only, please include over breaktime in calculation.

End of office hours 6:00 pm, strickly no over time

Can you please help me,

A1= time in

B1= time out

C1= time in

D1= time out

I want to calculate the late and under time,

Office start at 9:am w/30 mins Grace period,

The break time is one hour only, please include over breaktime in calculation.

End of office hours 6:00 pm, strickly no over time

Very new in this board. If I post this question in the wrong forum please bear with me.

There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year Jan-Dec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from Feb-Dec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.

Thanks and sorry for this long questions

There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year Jan-Dec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from Feb-Dec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.

Thanks and sorry for this long questions

Good day... I need an IF Function that will allow me to action a time in a time range:

... If the time 04:16 falls in the time range 04:00 - 04:29, than put a one (1) in the filed x...

... If the time 04:16 doesn't fall in the time range 04:00 - 04:29, than leave the x fiel empty

Any help is appreciated.

... If the time 04:16 falls in the time range 04:00 - 04:29, than put a one (1) in the filed x...

... If the time 04:16 doesn't fall in the time range 04:00 - 04:29, than leave the x fiel empty

Any help is appreciated.

I would like to send an Excel file with hyperlinks of photo's attached to cells but when it is sent the recieving person cannot access the hyperlinks anymore. I am using 2007, I have tried to send the folder but that didn't seem to work. Any other way around this?

How would I create a system where an excel file is updated in real time with new values? I have a program which logs data, and I want to have excel open, plotting the data in real time. Is this possible?

I added a print button to my userform

Code:

This prints out the userform as I would like, however it autoprints to the default printer and doesn't allow any printing options so I can't select to "print to one page". So as of now it is only printing out half my form and cutting off the rest.

Any suggestions?

Code:

Private Sub btn1_Click() UserForm1.PrintForm End Sub

This prints out the userform as I would like, however it autoprints to the default printer and doesn't allow any printing options so I can't select to "print to one page". So as of now it is only printing out half my form and cutting off the rest.

Any suggestions?

i've been trying to figure this out on my own but seem to be hitting road blocks.

i have a column of numbers that we extracted from a database as whole numbers. for instance, 30 should be .30 and 100 should be 1.00. when i try to increase or decrease the decimal point it doesn't allow me to convert it to those positions.

would any one have a tip of how i can easily convert this? there are over 2000 entries in my excel spreadsheet so to do it manually would take a lot of time/effort.

thank you!

i have a column of numbers that we extracted from a database as whole numbers. for instance, 30 should be .30 and 100 should be 1.00. when i try to increase or decrease the decimal point it doesn't allow me to convert it to those positions.

would any one have a tip of how i can easily convert this? there are over 2000 entries in my excel spreadsheet so to do it manually would take a lot of time/effort.

thank you!

I'm trying to come up with a solution to calculate the man hours and days worked over multiple sheets.

I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.

The days of the week consists of "Name" "Date" Hours"

The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"

Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.

I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.

I have attached a sample file.

Thanks in advance for your help.

I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.

The days of the week consists of "Name" "Date" Hours"

The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"

Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.

I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.

I have attached a sample file.

Thanks in advance for your help.

Hi Guys,

I need help....I am working production scheduling with Start and End time but there is a lunch time and tea time. Is there any formula to calculate Start time to End time with excluding lunch time?

Here is example :

Lunch time :12:00pm to 1:00pm.

Duration Process :6 hours.

Start Time (8:00am)

End time (5:00 pm)

I need a formula to calculate from Start Time 8:00am and what is the End Time? with 1 hour lunch time...

Thanks,

ET

I need help....I am working production scheduling with Start and End time but there is a lunch time and tea time. Is there any formula to calculate Start time to End time with excluding lunch time?

Here is example :

Lunch time :12:00pm to 1:00pm.

Duration Process :6 hours.

Start Time (8:00am)

End time (5:00 pm)

I need a formula to calculate from Start Time 8:00am and what is the End Time? with 1 hour lunch time...

Thanks,

ET

Hi all,

I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise.

I've written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer.

Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly.

It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly.

I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine. It is the same Operating system and the same version of Excel.

I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware.

I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out!

Thanks very much for your time.

I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise.

I've written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer.

Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly.

It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly.

I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine. It is the same Operating system and the same version of Excel.

I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware.

I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out!

Thanks very much for your time.