Automatically Populate 12 Month Calendar With Result Depending On Start And Finish Dates

Mar 26, 2014

I've just started working on an FTE calculator and wish to populate a 12 month calendar with FTE depending on the start and finshed dates.

FTE Calculator non nursing.xlsx

I've attached the file. In Column D the user would select the month the staff start and in the Column E the month the staff will finish. I would like the fte that is calculated in Column Z then to populate in the 12 columns AB:AM (Jul to Jun) with corresponding month start and finish.

View 2 Replies


ADVERTISEMENT

Calculate Time Elapsed Between Start And Finish Dates

Dec 13, 2013

test.xlsx

I need to calculate the time elapsed between start and finish dates. I've figured the TODAY function, as explained here but I'm referring solely to dates in cells. How to?

View 1 Replies View Related

Number Of Working Hours Between 2 Dates Including Start And Finish Times

Mar 22, 2007

I have 2 dates+ times.

1 the contract start date and time.

2 the contract end date and time.

Can I calculate the number of working hours between these two using a formula?
So if I have a 10 hour working day (08:00-18:00), Mon-Fri and the two Dates/Times can I calculate the number of working hours?

Example
Start 06-Mar-07 10:00
End 14-Mar-07 14:00

This is 2 part days and over a weekend.

View 9 Replies View Related

Auto Populate Calendar Days Based On Month / Year And Auto Insert Work Based On Dates / Name

Jul 31, 2013

I am trying to auto generate a calendar based on two drop down menus - Month and Year.

Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.

August PM Schedule Demo.xlsx

View 2 Replies View Related

Configuring Dates: Enter A Date In One Column, Another Column Will Automatically Populate With The 1st Of The Next Month

May 8, 2009

how to make a certain type of date automate. It's kind of hard to explain, but basically, I'd like to make it so that when I enter a date in one column, another column will automatically populate with the 1st of the next month. For example:

If I enter 4/26/2009 in the 1st column, column 2 will read: 5/1/2009
If I enter 1/19/2008 .................................................. 2/1/2008

Also, it's very important that if the FIRST date is already the first of the month, then the second column will read the same. For instance: If I enter 3/1/2009 in the first colum, the second column will ALSO read 3/1/2009.

View 3 Replies View Related

Changing Calendar Start And Stop Dates

Jun 20, 2007

The company I work for does not use the usual calendar dates and uses a modified calendar. As an example, the month of January is Dec 31 thru Jan 27, February is Jan 28 thru Feb 24 and so on. I need to group data using a pivot table and summarize data by month, but as I just described above, calendar months will not work. Is there a way to modify what Excel sees as monthly dates?

View 9 Replies View Related

Populate Cells/Table Depending On INDEX Result

Feb 7, 2007

I am trying to populate a table (a7:c27) in the attached sheet with data.

First, I must enter a number 1-16, which uses index function to return either "series" or "base"

If it is base, i want the number in the corresponding table filtered down - i.e. if 1 originally picked then h7 is (40) is entered into each month as base = constant.

If the result is series, then the data in i7:z7 is entered into the the 18 month table.

View 3 Replies View Related

Automatically Get All The Dates Of A Month Entering Specific Year And Month

Dec 1, 2012

In a sheet I enter the following:

... in A1 a year (say 2012)
... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)

How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?

View 3 Replies View Related

Return Start And Finish Date?

May 25, 2014

I am trying to return start and finish date of events depending on when resources are allocated.

View 4 Replies View Related

Get Start And Finish Time For Breaks / Lunch?

Apr 15, 2014

I am trying to get the start time and finish time for breaks.

I have 4 columns Name, status, start, finish. In the name column there are multiple names, under status there 6 different status's. and under the time columns there are in and out times dependant on status. I am trying to us index match match. I have tried vlookup and hlookup as a nested function. I keep getting errors.

NameStatus MessageStart TimeFinish Time
Cameron WilliamsTech All & Billing12:31:4812:31:52
=index(starttime,match(name,namefield(match,"on lunch",starttime)))

[Code]......

View 1 Replies View Related

Mark The Hours On A Start And Finish Time

Mar 25, 2014

I am working on an excel spreadsheet for work and I have to show a chart that shows the time of day a space is in use. Right now I am having to mark all of these by hand which takes me forever because I have a couple thousand lines of information.

I need to mark the hours in use based off the start and finish time.

For Example:
1) If the start time was 7:00 and the end time was 10:45 then the markers would only mark 7am and 10am
2) If the start time was 7:15 and the end time was 10:00 then the markers would only mark 7am and 9am
3) If the start time was 7:30 and the end time was 10:30 then the markers would only mark 7am and 10am
4) If the start time was 7:45 and the end time was 10:15 then the markers would only mark 8am and 9am

I was given the formula and it works to count the whole hour but now I need the formula tweaked and I don't know how...I need the formula to account for quarterly hours...and I don't know how to fix it.

Attached is an example of the spreadsheet of what I currently have and of what I need. Book1.xlsx

View 2 Replies View Related

Calculating Start And Finish Times For A Process

Feb 22, 2010

I have a number of processes that I would like to calculate the finish times of.

For example, Process A may take 18 hours to complete, Process B may take 28 hours to complete and Process C may take 125 hours to complete etc

However these processes only run during certain times, i.e. 9am to 5pm.

Ideally I would like to setup a spreadsheet that when given the start time and process time calculates the (date and) time the process will finish.

I can get it to work on a 24 hour day but I've been tearing my hair out trying to take out the none-working part of the day (the 5pm to 9am).

View 9 Replies View Related

Providing A Start And Finish Cell Into A Range

Jan 23, 2007

I need to create a range of cells in Column A. I know the first cell, A2. The final occupied cell will vary. Once I have the range object I would like to step through and analyse each cell in turn using a For/Each loop.

The code I have so far is:

Public all_structures As Range 'Range required for for/each loop Column
Public last_structure As Range 'Last cell in Column
Public molcell As Range 'Current cell in range

' Establish range of cells in Column A

Set last_structure = Range("A2").End(xlDown)
Set all_structures = Range(Range("A2"), Range(last_structure)) ***

' Loop through each structure in turn
For Each molcell In all_structures

Loop code In here

Next molcell

When I run I get an error message of "Run-time error '1004': Method 'Range' of object '_Global' failed". It does not like the line marked ***.

View 3 Replies View Related

Auto Fill Dates: The Sheet To List The Dates In A Column For Each Month Automatically

Jun 17, 2007

I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.

View 6 Replies View Related

Automatically Generate Promotional Calendar From List Of Dates

Sep 10, 2013

I have a marketing calendar that I need to update just about daily with new promotional plans because the dates change very often and there are a lot of markets that we are keeping track of. Because the format of the excel is basically a bunch of merged cells to show the range of dates of a particular promotion, it takes a very long time to unmerge and remerge everything when one date changes.

I have an example of what the graph looks like below, as well as a grid of what I would like to use to generate this graph automatically using the start and end dates so thats all you need to change. If you changed the start dates, the market name, or the promo name, the chart would reflect the updates.

View 2 Replies View Related

How To Calculate Work Hours From Only A Start And Finish Date And Time

Feb 28, 2013

I can calculate total hours when a user enters a start date/time and a finish date/time. The kick is I only want to include hours from 2:00 PM to 12:00 AM (10 hour period). So assuming all the start and end times will be in this range, how can i calculate work hours over multiple days? For example: Start date/time = 2/26/13 2:30 PM and end date/time = 2/28/13 10:30 PM. I want my calculated hours to show 28 hours.

View 1 Replies View Related

Automatically Populate Dates ...

Mar 28, 2009

I've asked this similar question on another message board (the Microsoft help community), but failed to follow-up quickly enough for the support I've come here to ask for. My situation involves a spreadsheet that is used to track days worked over the course of a week. I have the days of the week listed in one row (Sun - Sat) and in the row above, a place to put the day (i.e. - 3/28). I asked for help with a macro that would allow the user to place the date into any of the cells above the days of the week, check to ensure that the corresponding day of the week is correct, and if so, populate the other remaining empty dates automatically. Below is the macro that was kindly provided. It works perfectly as long as my data is in the referenced ranges (A1:G1, etc); however, my actual data is in range L6:R6 for the dates and L7:R7 for the day of the week. (example below). I've been able to pick apart the code to understand how to change most of the macro to accept input from the different range; however, I'm unable to comprehend what I'm missing to complete the modification.

View 6 Replies View Related

IF/VLOOKUP (set Up A Spreadsheet Which Automatically Calculates The Start And End Dates Of Project Tasks)

Feb 1, 2009

I am trying to set up a spreadsheet which automatically calculates the start and end dates of project tasks, by looking at the order in which tasks need to be completed. I have attached a spreadsheet to show what I am trying to do.

View 3 Replies View Related

Excel 2010 :: Populate A Daily Calendar Using The Dates Between Date Of Arrival And Date Of Departure

May 30, 2013

I'm using Excel 2010. I need to populate a daily calendar with the number of nights spent, extracted from the Date of Arrival and Date of Departure of individuals.

View 2 Replies View Related

Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)

Dec 23, 2008

I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...

View 4 Replies View Related

Subtract Start Time From Finish Time Return Hours Worked As Number

May 5, 2006

I have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!

For example
Start time: 21:00
Finish time: 06:30
Hours worked: 9.50

Start time: 12:30
Finish time: 23:00
Hours worked: 10.50

View 9 Replies View Related

Calculate Start Date From Finish Date Minus 14 Working Hours

May 7, 2014

I needs a formula (not VBA) to calculate the required start date.

I have to do a job of 14 working hours and this job must be finished on 05-may-2014 13:00

My working week is from monday u/i friday and every day I work from 08:00-16:00

At what time do I have to start the job to get it done in time.

The formula should give this result: 01-may-2014 15:00

View 3 Replies View Related

Subtract The Time Finish & Time Start

Sep 25, 2009

I have a workbook that is generated from the system(AS400)but it wont subtract the time finish -time start. I get a #Value error in the cells I tried two different formulas.

View 2 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

Print Result Cards Automatically From Result Sheet

Apr 25, 2014

I have excel result sheet which contains students information. i.e. name, subjects and their corresponding marks, grade, percentage etc. So from that sheet I want to print result cards for each students separately from the data (result sheet).

View 4 Replies View Related

Calendar Control On Excel Worksheet - Default Start Date

Jul 9, 2012

I have set up a calendar control on a userform and got it to pop up when selecting one of 3 cells on a worksheet. The various bits of code making this work are below.

This code is attached to the Userform.

VB:
Private Sub Calendar1_Click()
With ActiveCell
.Value = Calendar1.Value

[Code]....

What I am struggling with is that I'd like to have the calendar that pops up in cell C18 (which is Date of Birth) default to starting on 1st January 1987 not todays date. Also, if there is a value in either of the 3 cells that use the calendar, then I'd like the calendar to display that date. If the cell is empty then todays date is fine for cells G3 and G26.

View 7 Replies View Related

Calendar To Populate Textbox In Userform

May 1, 2006

I've created a userform to populate rows in a worksheet. The userform gets details of flights with inbound and outbound dates. I'd like a calendar to popup so the user can just select a date with the click of the mouse which then resides in the textbox until sent to the sheet. I know how to get the calendar to pop up when entering directly into the sheet but I don't want the user to have to touch the sheet unless making amendments.

View 9 Replies View Related

Automatically Bold And Highlight The Current Month’s Total And Month Name

Jan 21, 2010

I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name.

Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?

View 9 Replies View Related

Interactive 3 Month Calendar

Feb 13, 2007

I'm looking to have an interactive calendar created that when a specific date is entered in a cell,...example(A1)

The calendar at the top of the screen will reflect 3 months out (in a standard box looking format with the starting day highlighted in Bold. Imagine 3 calender boxes (always 3 months) and when you enter a date in cell 1,...(say 6/07/07, the calendars at the top adjust automatically so that the first box now reflects June and has the 7th day in Bold...the other two boxes now obviously reflect July, and August.

View 9 Replies View Related

How To Get Data Entered In Form To Auto Populate Into Calendar

Jun 26, 2014

I am trying to build a Calendar that pulls all of it's entries from data submitted via user form. To be more clear, I built a basic user form in VBA where I can submit this data: name, brief description, Time/duration, and date. Instead of the data I enter into the form populating in specific cells in my Excel workbook, I would like for the information to be populated into a calendar based on the date.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved