Time Allocation Calendar

Jul 12, 2007

I am creating a time allocation / holiday tracking calendar. 150 employes (1 per column) and then using rows (10 - 375) as a daily track. I am having trouble with two aspects and would value your assistance.

1. i have locked the top 9 rows and would like row10 to jump automatically to todays date. i.e current info directly below the titles block

2. How do i make the calendar wrap around i.e. if someone puts in for a week holiday starting dec 31st 2007, how do i make the calendar roll round to 2008.

ADVERTISEMENT

Time Difference With Specific Calendar

Feb 5, 2009

I am looking to calculate the time difference between 2 date time fields using a specific calendar.

I work in an engineering company and am having problems calculating machine utilization efficiencies.

Example:
Job 1: Start date 06/01/2009 10:00:00
Job 1: End date 06/02/2009 12:00:00

Calendar:
Dayshift: Monday - Thursday 07:15:00 - 16:15:00, Friday 07:15:00 - 12:15:00
Nightshift: Monday - Wednesday 21:00:00 - 07:45:00, Thursday 21:00:00 - 05:45:00

Using the specified calendar I am trying to calculate the time difference between the start and end date of job 1.

View 11 Replies View Related

Excel 2007 :: Dropdown Calendar - Date And Time Picker

Apr 13, 2013

I want a drop down calender to choose date from and found that i can use More Controls> "microsoft Date and Time Picker 6.0 (SP4)" for excel 2007 but it doesn't work with other excel and shows a "x" not recognised in some other machines.

View 1 Replies View Related

Excel 2010 :: Calendar - Set Up Specific Time Frame And Due Date To Exclude Weekends And Holidays

Apr 9, 2014

I'm using excel 2010 on a pc. I need to create a calender. So far I have been using the tutorial for the pop up calender. The result I'm looking for is to set up a specific time frame and have the calender provide the "due" date. I would like the due date to exclude weekends and holidays. Would I be able to do this with the pop up calender?

View 3 Replies View Related

VBA For Stock Allocation

Jun 13, 2008

Let say I have 5 shops: A, B, C, D, and E

They request for stock replenishment from warehouse as follows:
A: 4 pieces
B:2 pieces
C:3 pieces
D:3 piece
E:3 piece

But since the warehouse only have 12 pieces of this product so need to allocate to the shops in rounds that is:
Round 1: one piece is allocated to each shops (Stock left=12-5=7)
Round 2: one piece is allocated to each shops (Stock left=7-5=2)
Round 3: one piece each is allocated to A and C (the priority should be B but since B just requested for 2 pieces only which has been fulfilled) since there are no more stock

how to write the scripts to do this allocation.

View 9 Replies View Related

Calendar Control 11: Selected On The Calendar Is Greater Than 12 The Date Is Entered Correctly Onto The Sheet

Jan 8, 2010

I have a program where I can update the calibration due date of an item. I have attached a cut down version of my program showing the relevant areas. There is usually password protection on the worksheet so it can only be edited via the form (the vba coding removes the password protection before editing, then re-enables the password protection after editing). The "Update Calibration" button is usually on a "Menu" sheet.

Once the form is opened a serial number is typed in the textbox. The calendar button is then clicked, which brings up another form with the calendar on. The due date is selected on the calendar. When "OK" is clicked, the date label caption is then changed to the selected calendar date. When "Submit" is clicked, the spreadsheet will search for the Serial Number, once found, the label caption (being the date selected) will be entered into the cell to the right of the serial.

If the day selected on the calendar is greater than 12 the date is entered correctly onto the sheet. example: calendar date selected = 15/01/2010. shown on sheet as 15/01/2010. However, if the day selected on the calendar is 12 or less, the date is for some reason entered incorrectly onto the sheet. example: calendar date selected = 08/12/2010. shown on sheet as 12/08/2010???? What is going on here? how come the day and month are swapped around if the day is less than 12????

View 4 Replies View Related

Excel 2007 Calendar Control - Select Date From Calendar In A Field

Dec 24, 2009

Using Excel 2007 Calendar Control:

I want to select a date of choice from a calendar in a field within an Excel spreadsheet.

I do not know how to do userforms and so that is why I wonder if I can add this in just a spreadsheet.

This is how far I have gotten so far: After doing the following, I have a static calendar setting in my spreadsheet like a text box showing the current date.

Developer
Insert
More Controls
Calendar Control 12.0

View 9 Replies View Related

Auto Allocation Of Data

Jun 29, 2013

I have a list of 500 'cases' for staff to work on and I want to auto allocate them to people based on their skills. Whilst I can do this easily with each person have their own page in my workbook, I'm having the issue of the same data being replicated onto multiple sheets.

The main data is on one page and each person has their own individual page for their cases for the day. Is there any way to avoid multiples being sent out whilst creating a kind of 'priority ladder' as such?

View 8 Replies View Related

Allocation Of Permutations In Excel

Nov 26, 2013

I have trying to assign cards to respondents to view. I have 16 cards total which are grouped in sets of 4. Each set (of 4) is given a color, (red, green, blue, and yellow). Each color has a 4 different shades (S1, S2, S3, S4) so I have the following 16 cards:

1: Red_S1
2: Red_S2
3: Red_S3
4: Red_S4
5: Green_S1
6: Green_S2
7: Green_S3
8: Green_S4
9: Blue_S1
10: Blue_S2
11: Blue_S3
12: Blue_S4
13: Yellow_S1
14: Yellow_S2
15: Yellow_S3
16: Yellow_S4

I will have a total of 10 respondents giving their opinions who will each see 5 of the cards, randomly. Each respondents must see each color at least once (given that 5th card will be a repeated color). Each card must be tested 3 times (given that the 2 additional cards can be any of the cards).

If there a function or cell code that I can write so that I can allocate the card #s (1-16) across my 10 respondents?

View 1 Replies View Related

Random Distribution/Allocation

Nov 1, 2009

I have a spreadsheet which has a list of staff (column B) and work categories (row 3).

Row 4 is updated each day with a count of the incoming work for each work category.

I want to be able to randomly distribute (as evenly as possible) the no of work items in Row 4 between the doers (B7:B13). Each item of work also has to be checked so I would also like to randomly distribute the checking between the checkers (B16:B18)....

View 9 Replies View Related

Coming Up With Inventory Allocation Formula

Aug 4, 2014

I've been assigned a project where I'm suppose to report inventory allocation to projects at end of day every Thursday. The past 2 Thursdays, I've just literally been manually plugging in the numbers into the allocated stock column.

The spreadsheet I came up with is attached. The 1st tab is the demand showing the needed quantities per item and respective due/on-site dates. The on-hand inventory (stock) is on the 2nd tab. My current process is:

1. once items have been shipped, I delete those entries/rows from the first tab. This way, only all the outstanding/open orders remain.
2. after completing #1, I then copy and paste on-hand inventory from our database (Navision) onto the 2nd tab.
3. I manually plug in the the qty for each item depending on stock available. This is the part that I would like to streamline.

Inventory Allocation.xlsx

View 2 Replies View Related

Formula For Batch Allocation Or Split Up?

Oct 2, 2013

I need a formula for batch allocation automatically.

See the attached file : Batch_Allocation.xlsx‎

View 5 Replies View Related

Changing Cost Allocation Macro?

May 23, 2012

I am trying to write a Macro to allocate a certain cost across thousands of orders. The number of orders will obviously change monthly. The cost is relatively small, and when divided evenly by the total orders needs to be rounded up to the nearest cent (Typically $.01 - $.02)

I then need the cost to be insered in a colum running next to a total cost column, but I need it to stop when it reaches the total cost and just start adding zeros to the rest of the orders so as not to overcharge.

Not sure if I need to incorporate solver somehow. I figured I would reference the allocated cost from a cell in the worksheet but not sure how to insert any algebraic logic into a macro.

View 9 Replies View Related

Fill Vehicle Allocation Table

Nov 26, 2006

I am reposting this as my previous post was deleted.. i dont know the real reason for the same...

The raw data in the attachment contains:

•C1 to AI1 refers to vehicle numbers.
•Column A refers to Shift Time
•Column B refers to Number of vehicles required in that particulars Shift time

The output:

•As you can see in the Sheet “Output Required”, fields marked in the color “light green” are manually allocated vehicles numbers available and required according to shift time

•The logic used behind allocation is the same vehicle should not be used before or after within the span of 3 hrs, the gap between the vehicle usage before and after should be minimum 3 hrs. And the maximum time gap can be any value.

•Vehicle usage can be maximum (ie. You can use the same vehicle by rotating within 24 hrs but keeping in mind that it is not used before 3 hrs).

• all the available vehicles must be used before the same used vehicle is used in the new shift... ie. You should utilize all the vehicles before one is duplicated

View 9 Replies View Related

Allocation By Daterange - Single Cell

Apr 27, 2007

I m creating a single cell formula in 'report'B3 that would allocate 'data'D3 appropriately by month heading if copied across. A few other threads on this topic only acheive this by creating an addtional row, not in a single cell. Since I'm going to use this on a report in Access, I just need the calculation. Is it not possible to acheive this in a single cell?

View 2 Replies View Related

Revenue Allocation Using Combined/Nested IF Statements

Jul 24, 2008

I have been asked to go through some information which has over 200 000 lines, what I need to do is as follows:

where order number is unique, revenue source = "Unique"
where order number appears more than once, check division to see if it appears in more than one division, if it does revenue source = "Cross Selling", if it does not then revenue source = "Divisional Package"

I need all of the above in formula line which will be inserted in the revenue source column

I have attached the file with a sample of the information I am using.

View 9 Replies View Related

Summarise Data After Applying Allocation Split

Feb 25, 2009

The task involves summarising about 10,000 lines of data into months by Plant by Process. I can achieve it by stepping through a couple of schedules but I'm sure it can be done using one formula (but I can't find it!).

View 8 Replies View Related

VLookup With 2 Criteria - Provide Log Of Staff Allocation

Aug 31, 2012

I'm fairly new to excel and trying to create a simple spreadsheet to provide a log of staff allocation. I've created a first a sheet displaying a table with a week of rota. The first column lists where the staff are placed, the first row the date and inbetween the staff member covering that date. There are 5 further sheets, one for each member of staff with a simple table - first column is date, second is allocation.

Back on the first sheet below the timetable is a further grid showing on the first column staff names, and below each date a formula =VLOOKUP( B$3,David!$A:$Z,2,FALSE) pulls information from the 5 sheets displying whether a memeber of staff is on leave or there allocation on that particular day.

The problem i'm having is trying to get the 5 simple staff sheet to pull data from the Rota ie. I need say B3 to look at the date in A3, find the same date on the Rota sheet, look for the relevent staff name below it (if present), and display the allocation from the first column on the same row as the staff member.

I've tried a combination of Vlookup with Match and Index with Match but nothing seem to work.

View 7 Replies View Related

Phil Johnson's Calendar - Pop Up Calendar Without ActiveX

Mar 7, 2009

I am using a great pop up calendar without ActiveX made by a guy called Phil Johnson. When you click a specified cell the calendar appears. The only problem is that the calendar doesn't default to today's date when it opens.

I have the original file Calendar source code if anyone needs it.

View 4 Replies View Related

Input Allocation Percentage Dependent On Number Of Variables

May 15, 2014

On the attached sheet in column X, I am trying to input the Allocation percentage dependent on a number of variables:

Type (Bar, Blank, Coil, Sheet, Special)
Sub Fam Code (2A02, BC02-05, BC99, BZ99)
Sup Category (Arcelor, Independent, Tata/ Corus)

The allocation percentage is based on the below; anything outside of this should result in "Other".

Sheet (BC02/03/04) A=40%, T=40% & I=20%
Coil (BC02 & BC03) A=40%, T=40% & I=20%
Coil (BC04) A=30%, T=50% & I=20%

Example 1:
Type - Sheet & Coil
Sup Category - Independent
Result 20%

Example 2:
Type - Coil
Sup Category - Tata/ Corus
Sub Fam Code - BC04
Result 50%

Example 3:
Type - Coil
Sup Category - Arcelor
Sub Fam Code - BC04
Result 30%

View 1 Replies View Related

Excel 2010 :: Fails To Release Memory Allocation

Jan 27, 2014

I am running Excel 2010 32bit under Windows 7. The Excel file worked perfectly under Excel 2007 and Windows XP. The Excel file is somewhat complex with numerous links and embedded cell formulas. Occasionally, I get the following error message:

5.jpg

Before I install the 64 bit version, I attempted to chack a few other things. I opened the task manager and while viewing the process tab, I opened my Excel file. Once the file loaded, this is the allocation displayed in task manager:

1.jpg

This seemed reasonable considering the size of the Excel file (33Mb). Without changing anything, I clicked save to make sure it was working properly. While viewing the process, I watched the allocation climb, substantially. when it completed, this is what the process displayed:

2.jpg

Continuing to click the save button, without changing anything, continued to increase the memory allocation until the error message was displayed:

3.jpg

4.jpg

Closing Excel and re-openeing the file resets the memory allocation back to normal, which is a popular solution to this issue in the forums.

I did check the same issue using Excel 2007 and the memory allocation releases after each save.

I can see how installing the 64 bit version of Excel can correct this issue, and with 12Gb of Ram, I will probably use this method until I can get this fixed.

View 3 Replies View Related

Populate Calendar If Date In Associated Cell Show Date On Calendar

Apr 4, 2013

I currently have a worksheet that when a user clicks on the cell, a calendar form opens. From here they can select a date, click ok, and the date gets placed in the corresponding cell they clicked on.

Now if the user needs to change that date, i want them to be able to click on the cell again, and when the calendar opens, the date in the cell would be selected on the calendar. I was able to do this in a userform, but im not sure how to edit it to work on the worksheet. I am using the below to populate the calendar.

Code:
private sub worksheet_selectionchange(byval target as range)
if not intersect(target,range("aa16:aa24")) is nothing then .show calendar
When the user clicks the date, the below code places it in the cell.

Code:
private sub ok_click()
with active cell
.value=calendar1.value
end with
unload me
end sub

Now the below code was used when I was using a userform, but im not sure how to edit this to pull the date from the active cell.

Code:
privatesub userform_activate()
me.calendar1=date
if not tb is nothing then
if isdate(tb.value)then me.calendar1.value = tb.value
end if
end sub

View 1 Replies View Related

Display End Time Automatically Upon Entering Start Time And Time Usage

Dec 19, 2008

I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?

View 2 Replies View Related

Can I Add A Calendar

Feb 22, 2008

I have a spread sheet that requires people to put dates in certain cells. I was wondering if it is possible to have a small calendar pop up when the cell that requires a date is clicked on. The calendar would need to show the current month, and future months for the calendar year. I’ve seen this feature in OneNote 2007, and wasn’t sure if this same feature could be created for excel. I’m not sure if this can be done by a macro or not.

View 9 Replies View Related

Calendar Into Log For Many Things

Nov 3, 2008

I have a few excel calendars.

I was wondering can I take a calendar and then put hyperlinks or vba to make a sheet if clicked on.

So If I click on an icon on a date, it will copy a certain template sheet, then link it to that date and that icon.

See an excel calendar, I have a few others to play with aw well.

But this one gets updated on the same the page and not sure how the links/icon would follow the dates. I was hoping for maybe 3-4 different icons on a date.

View 7 Replies View Related

Scheduling For UAE Calendar

Jan 16, 2009

Attached is the file wherein I am trying to make the scheduling gantt for UAE calendar where the weekends are Friday and Saturday. I am not able to format sundays as workdays some how through my conditions.

View 2 Replies View Related

Make A Calendar

Jul 23, 2009

Is there any simple way to build a calendar in Excel? Ideally, all events will be listed on one sheet by date and the calendar will display the events on their respective dates. Is this possible?

View 2 Replies View Related

Calendar Not Working?

May 23, 2013

Got this neat Calendar from another thread on this site,but it has problems. Played around with what little skills I have with no luck. When you open the calendar go to Jan, 1992 click on first four days and everything works. When you click on Thu 2 the problem begins.

View 14 Replies View Related

Calendar Macro

Sep 19, 2007

I have the following Calendar Macro. I want this to pop up when cell A5 is active vs assigning a Macro Button.

View 9 Replies View Related

Calendar Popup

Oct 6, 2008

Borowed This Code From The Site And Indeed It Comes In Very Handy .....

View 9 Replies View Related

Calendar Pop Up In 2000

Oct 17, 2008

I created a calendar control pop-up in Excel 2003.

To create, I did the following:

First: I went to Insert>Object>Calendar Control.

Second: I inserted following code

View 10 Replies View Related

Run An Outlook Calendar

Nov 17, 2008

Is there a possibility to run an Outlook Calendar in Excel via code? and share it between users.?

View 2 Replies View Related

Better Calendar Control

May 20, 2009

I'm trying to make it so that when a user clicks on a certain cell, a calendar pops up so they can pick a date. This seems like a pretty common thing to want; I hope Microsoft puts it into the next release.

Anyway, I've read the tutorial found at [url]which tells you how to create a userform, add the calendar control to it, etc.

I've also modified it so that the calendar comes up when the user clicks on a certain cell, and so that the form closes when they choose a date.

I had to use the selection_changed subroutine to tell if someone clicked on the cell, but there are some flaws.

First, moving over to the cell with the keyboard arrows brings up the calendar (undesired result; I only want it to come up with clicking)

Second, if the cell is already selected, clicking it doesn't bring up the calendar since the selection didn't change (also undesired; I would like the form to come up whether the cell was previously selected or not).

View 12 Replies View Related

Add Popup Calendar

Jun 2, 2009

Is it hard too add a popup calendar too my date box. Can it go too year 2075?
When you open the calendar you could click on date too install.

View 3 Replies View Related

Calendar Formula

Oct 24, 2007

I'm trying to make a calendar to log the following throughout the month:

Steps =SUM(C6:G34)
Aerobic Steps =SUM(C7:G35)
kcal =SUM(C8:G36)
Miles =SUM(C9:G37)
Caloric Intake =SUM(C10:G38)

Now the above would be entered on each corresponding day throughout the month and I would like to keep a running total at the bottom. I thought I had gotten the formula worked out, but in the slots at the bottom there is a total of 152 but there aren't any numbers entered for it to total so what I did was place a -152 after the above formula which brought it to 0 then started to enter my numbers but they were off by 30 I think it was.

View 11 Replies View Related

Add Calendar To Userform?

Feb 29, 2012

Trying to add calendar to userform but can't find the add in control

View 2 Replies View Related

Drop-Down Box With A Calendar

Aug 2, 2002

Is there a way to set up a designated cell in a spreadsheet with a drop-down box that will have a calendar, so that when you click on a date in the calendar it puts that date in the cell?

View 9 Replies View Related

Incorporate Calendar

Feb 21, 2007

I have the following worksheet ,named "Calendar", to make an annual calendar and I'm wondering if there's a way to use it to make a calendar in worksheet named "NovemberDaily". On the worksheet named "NovemberDaily" I want to put in cells C14, G14, K14, O14, S14, W14, and AA14 "11/1/2007" if the weekday of the week matches the date"11/1/2007". Is this possible or is there an easier way of accomplishing this. I'm needing the date (e.g. 11/1/2007) in the cells mentioned because it ties into another formula that I'm using later in the worksheet.

View 32 Replies View Related

Created A Pop Up Calendar Using VBA

Dec 3, 2008

I have created a pop up calendar using VBA with the help of VBA Tips at www.fontstuff.com/vba and it works very well. However it has the default date of 12 March, In the tutorial that i used there was some code to use todays date if the cell (in which i was going to put a date) is empty. Which is as follows

Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
End Sub

yet when the calendar does pop up, the date remains at 12 March (the calendar in the visual basic editor reflects the date correctly). I've tried to change numerous amounts of properties, without success

View 9 Replies View Related

Automatic Calendar Using VBA

Feb 9, 2009

provide an input (1,12, or 123 would be the only input selections), then the vba needs to hide all rows that are not equal to the input

I have developed a type of calendar in excel with the following setup. There are five key headers and each header has a code it pertains to, either 1,12, or 123. The spreadsheet is set-up like so....

Column
A B
Key Project

12 A
1 B
123 C
12 D
12 E
123 F
1 G

So basically vba code that will produce a pop up and ask for a key then after the key is entered it will display rows that have an equal key in column A and hide all other rows.

Fromt he above ex. if "12" was entered the spreadsheet would show:

12 A
12 D
12 E

View 9 Replies View Related

Calendar Not Visible

Jun 11, 2009

I have a userform which has a calendar in it (created in another userform).

When a user opens the userform, the calendar doesn't show up. But it works fine in my system.

I have already copied the MSCAL.OCX file in the user's system. But still the calendar doesn't show up. What other file do I need to give the user so that the calendar works.

View 20 Replies View Related

ADVERTISEMENT