List Of Weeks From Start Date And Input Number

Apr 16, 2014

I am trying to create a simple set up where the operator can enter a starting date and the number of weeks they want this event to occur. This then produces a column of dates from the starting date to the end date for the number of week entered.

E.g. - start date = 16/04/2014 - No. of Weeks = 3

The output would be as below

16/04/2014
23/04/2014
30/04/2014

View 4 Replies


ADVERTISEMENT

Calculate End Date From Start Date & Number Of Weeks

Jun 26, 2007

Take a look at the attachment file. Those highlighted in yellow are entered by the user. What is the formula to calculate the End date in (A6) after the user has entered the start date (A2) & the number of weeks (A4)?

View 5 Replies View Related

Excel 2007 :: Create Bar / Count Weeks And Colour Bar From Input Date?

Feb 14, 2012

I am trying to simplify a type of gannt chart bar across a spreadsheet. The spreadsheet has dates across row 3 that are calculated from the first cell F3 with =F3+7 to populate the rest of the row with dates. I want to be able to input a start date in D4, then all the other titles in column A. This start date will start the coloured bar at that start date in the chart, I will then copy/drag that start date cell for however number of weeks for the duration and I want the finish date to auto populate E4 with the finish date and also auto populate the numbers of weeks in C4 for that bar.

Excel 2007ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANA
OAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZ
CACBCCCDCECFCG1D42342McArthur River Mine Power

[Code]....

View 9 Replies View Related

Add Cell Number To Date & Add Weeks

Aug 19, 2007

I want to add a numeric number eg: 4 to a date format eg: 15/08/2007 so that it calculates 4 WEEKS from 15/08/2007 and returns the CORRECT date in a date fomat itself. How do i do this through a VB code ?

View 9 Replies View Related

Dynamic Dropdown List And Calculation Number Of Working Days / Weeks Remaining

Oct 30, 2013

I'm currently trying to set up a sheet ...

When setting up a Direct Debit There are 3 options, the 7th, 17th and 27th. I've put these into a drop down list. I need this list to be slightly dynamic. I must allow 10 working days from the date of the arrangment before I can apply the DD.

To explain further. If someone were to want to set up a DD and spoke to me today. If they then requested the DD be taken on the 7th, I wouldn't be able to set it up until the 7th of December as 10 working days are required. I'd like the list to reflect this. I can do it in OO with a simple if / switch but excel is a strange creature and I'm not sure how to approach the problem.

From that I think I also need to calculate the number of weeks remaining in the current financial year. I think I should be able to do this by using the Today() function and some maths.

View 1 Replies View Related

Convert Human-readable Text Strings Into Consistent Number Of Weeks From Today Date?

Dec 17, 2011

Is it possible to do the following with a formula in Excel...

I have a list of users with the dates they first logged into a system and the date they last logged in. I'm trying to group them together into segments so I can analyse them using a Pivot table and chart to see how often different groups are using the system, e.g. New customers in December, November, October, etc.

The trouble I am having is trying to convert the different human-readable text strings into a consistent number of weeks from today's date, e.g.

USER FIRST LOGGED IN LAST LOGGED IN
user 1 2 years 20 weeks 54 sec ago
user 2 44 min 7 sec 1 min 37 sec ago
user 3 49 weeks 2 days 17 min 3 sec ago
user 4 5 weeks 2 days 33 min 32 sec ago
user 5 38 min 9 sec 38 min 9 sec ago
user 6 5 weeks 3 days 1 hour 7 min ago
user 7 2 hours 17 min 2 hours 11 min ago
user 8 45 seconds ago 45 seconds ago

Is there a formula I can use to convert these human readble text strings into a number of weeks elapsed since today's date?

View 9 Replies View Related

VBA / Function To List All Dates Between Start And End Date Excluding Sundays

Feb 8, 2013

I need the VBA/UDF to produce a column of dates between a start date and end date, excluding Sundays.

I can see lots, excluding weekends, but very little for just Sundays.

View 4 Replies View Related

Calculate End Date Using Start Date And Number Of Days Excluding Specific Days

Nov 22, 2013

I want to calculate the end date of my German courses. This is how it works:

A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.

Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.

Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.

The workday function on excel cannot do this and I do not have any programming skills to work with VBA.

Legend:
*LU = lesson units; 1 LU is 45 minutes

Holidays:
28-Nov-13
29-Nov-13
08-Dec-13
09-Dec-13
25-Dec-13
31-Dec-13
01-Jan-14

[Code] ...........

View 7 Replies View Related

VBA To Select Multiple Weeks In Pivot Table Based On User Input

Mar 26, 2014

Have been going round and round on this! I have a set of data that shows sales dollars by weeks. Currently, that data is in a Pivot table. I'd like to have a macro that selects the weeks (1-52) based on my input. For example. If I want to see weeks 5-52, I want to be able to input "5-52" somewhere and have a macro run that will select weeks 5-52 in the pivot table instead of having to go in and manually select each.

View 1 Replies View Related

How To Set Worksheet To Auto Input Number When Item Selected From Dropdown List

Dec 6, 2012

I have a drop down box on a worksheet and once I have selected the item I want from the list, I would like the price of that item to appear in the cell next to it..

View 2 Replies View Related

Userform Validation If Date Is Within 2 Weeks Of Today Date

Mar 19, 2014

I am setting up some validation on my userform. In this case if a user enters a date within two weeks of todays date they will not be able to proceed onto the next page.

Example: Todays date is 19/03/14. If a user enters 25/03/14 into 'TxtDate' they will not be able to proceed.

[Code] ......

View 2 Replies View Related

Is Date Past X Weeks From Current Date

Apr 24, 2008

How would I write an if statement that checks to see if the date entered is two weeks past?

View 9 Replies View Related

Separating Weeks In An Order List

Apr 2, 2009

I order sushi at work for the office, and I want to automate the reporting side of it

this is what the information looks like (bad formatting, sorry) (the notes column is also void currently)

date name order # decscript Price Paid? Notes 1/01/2009 John Smith 10 blah 10 5 Y

Jane Smith 12 blah 12 7 Y

Adam Black 1 blah 1 4 Y

Charles Dee 1 blah 1 4 Y
15/01/2009 John Smith 10 blah 10 5 Y

Jane Smith 12 blah 12 7 Y

Adam Black 1 blah 1 4 Y

Charles Dee 1 blah 1 4 Y

Handel Fee 6 blah 6 8 Y

Gretal Goo 7 blah 7 6 Y
1/02/2009 Adam Black 1 blah 1 4 Y

Charles Dee 1 blah 1 4 Y

Gretal Goo 9 blah 9 7 Y

What I enter is the 1) person's name 2) their order # and 3) wether they have paid (Y/N).
The proper version has a vlookup to fill in the description and price.

What I want to do (but don't know how) is to automatically count the number of orders each week, and the value of each week's orders.

Well, actually I can do the count orders/sum value manually - more specifically I can't work out how to automatically seperate each week. The main problem being there's always an unknown and variable number of orders per week.

One solution is to have a fixed number of orders per week which _sort of_ works, but is clunky and inelegant. Plus the names don't autocomplete then as it has blank spaces.

View 12 Replies View Related

Calculate Number Of Weeks Stock?

Mar 12, 2014

Trying to think of a formula that will tell me the number weeks stock I have,would like to put the formula in h6 and copy across giving me the weeks stock

Green line is sales, yellow balance stock

EX1.JPG

View 6 Replies View Related

How To Show Number Of Occurrences In Last 6 Weeks

Oct 12, 2012

I am trying to create a table that will show the number of times a registration number has occurred in the last 6 weeks.

I can get it to display the values if I manually enter the rows that are in the last 6 weeks but I would like it to do it for me so other people can view the data without having to change values etc.

so Basically I have the date an entry was made in column B and the registration numbers are in column M. I have tried various combinations of COUNTIFS, SUM(IF and DCOUNT but to no avail! It either returns a 0 or an error.

BTW I am working with

=COUNTIFS('Event Tracker'!$M$605:$M$4999,A2)*('Event Tracker'!$B$2:$B$5000>=VALUE(O2))

*Where cell O2 is the date 6 weeks ago* at the moment which returns a 0 or the manual formula which is

=COUNTIF('Event Tracker'!$M$605:$M$4999,A2) where M605 represents the first entry 6 weeks ago.

View 8 Replies View Related

Displaying A Number As Weeks And Days

Jun 19, 2014

I would like to display a number as weeks and days. For example the number 8 needs to be displayed as "1w 1d" or the number 14 as "2w 0d"

I am able to use a formula such as =INT((C3)/7)&"w "&(((C3)/7)-INT(((C3)/7)))*7&"d" but I wish to preserve the formatting of a number so that I can add 2 or more numbers together.

I am hoping that there is a way of doing what I want using the Custom Category in the Format Cells dialogue box.

View 4 Replies View Related

Compute The FuTure Date When Start Date,Lead Date,Weekly Offs,Leaves And Holidays

Nov 15, 2008

First and foremost I would like to congragulate you on this wondeful piece of code in the below link...

The query was to get a future date excluding Fridays and Holidays...

http://www.excelforum.com/excel-work...rkingdays.html

I have a similar query and therefore I pasted this link...

I actually wanted to get a future date using a Dynamic two day off as my the offs keep on changing as well as incorporate Holidays and Leaves if any..

Now Holidays would be official Public Holidays and
Leaves would be taken by the employee..

The code needs to pick the Leaves + Holidays and different offs maybe even more than 2 offs...

View 7 Replies View Related

How To Use Input Boxes To Have User Enter Start And End Lines

Apr 28, 2014

How to set up a Inputbox to make the user pick the start line and end line for a loop

the sheet will have items on it numbered 1,2,3,etc I want a process done to the lines the user selects by item number not cell range.

line 4 to line 20 for example.

View 4 Replies View Related

VB Calendar, If Date Not Within 12 Weeks, Then Msgpop Up

Mar 23, 2009

I can get the calendar control to come up and the value selected on the calendar to show up in a certain cell.

If a user selects a date that is not within a certain 12 week range, I want a message to appear ("date not within 12 week program"), and the user can't select the date.

I am storing nutrition data from a certain date the user chooses and it is a 12 week program from the start date. I want to retrieve info from those 12 weeks but do not want them to be able to select data outside the 12 weeks.

View 9 Replies View Related

Calculate Date Difference In Weeks

Sep 21, 2007

On some Military computers the Add-ins were not installed, therefore the WEEKNUM function cannot be used.

The task is to calculate the time interval (in whole weeks) between two dates.
(Every start date is to be calculated as a whole week – same for the End date).

I tried but the most but came up with the incorrect formula in the attached workbook.

View 9 Replies View Related

Formula To Work Out Sum Depending On Number Of Weeks?

Jan 13, 2014

I have a spread sheet I have set up to calculate sales results, I have a column for each week in the quarter with the date at the top and a different page for each individual. I want to be able to enter a number to show how many weeks we have had and the spreadsheet to give me a cumulitive amount for the cells up to that date.

For example if I was to enter 1 in a cell A1 I would want the sum t work out just C3 for example.

If I enter 3 in A1 I would want the sum to be C3+C4+C5 wich is the three cells.

If I enter 6 in A1 it would be C3+C4+C5+C6+C7+C8 etc.

View 2 Replies View Related

V Look Up: Find User Number 1 And The Total For Weeks 1, 2, 3 And 4

Mar 13, 2009

I have a large spreadsheet with various data. On the first column I have a list of user ids, this is a unique id to each user. In each row there are data there are varies other data. I want to find a unique id and then add up the figures from week1, week2, week3 and week4.

So lets say I want to find user number 1 and the total for weeks 1, 2, 3 and 4, how would I do that using an excel formula? I have given an example below but I have simplified to spreadsheet.

IDWeek 1Week 2Week 3Week 4
139424236
255556044
334335434
461515350
529504694
618243830

View 2 Replies View Related

How To Auto-Populate Date Range (working Days Only) From Start Date And End Date

May 25, 2014

I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.

View 1 Replies View Related

Counting Weeks With Date Time Spans

Mar 21, 2007

I need to make formulas to count the number of weeks that each phase of a project takes, but I currently have the start and end dates of the phases. (See attachment)

View 5 Replies View Related

Date Differences In Days, Weeks Or Months

Jan 3, 2008

I've been racking my brain on this one for awhile, and I feel like I'm going the right direction but...

I have a list of dates like:
11/30/2007
10/31/2007
9/30/2007
8/31/2007
7/31/2007
6/30/2007
5/31/2007

or maybe

11/30/2007
11/23/2007
11/16/2007
11/9/2007
11/2/2007
10/26/2007
10/19/2007
10/12/2007
10/5/2007
9/28/2007
9/21/2007
9/14/2007
9/7/2007
8/28/2007

...and I need to determine if the difference between them is daily, weekly, or monthly. There are 79 of these lists, on 79 different sheets so I can't visually peg them as daily, weekly, or monthly.

The way I have been looking at the problem is to calculate the number of months between the last & first dates and see if the resulting answer (+1) matches the number of actual dates, and so on...

View 5 Replies View Related

Calculate Next Due Date Based On Start Date Frequency And Current Date?

Mar 9, 2014

I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.

Example

Initial Date :- 1st of January 2014

Frequency :- every 5 weeks

Current Date :- 9th of March 2014

Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)

I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)

View 4 Replies View Related

Formula To Calculate Number Of Work Weeks The Data Has Been Collected?

Dec 27, 2013

I am looking for a simple formula that would look at column A2-A100 and calculate the whole number for the number of the week I am on. So in cell K22, I would like it to add up the A column and spit out a number 4, then on Monday when I add a new record I would like it to automatically update to a 5; indicating the 5th week I have been tracking the data. This number is needed to calculate the average amount in a work week accurately and automatically in cells K20 & K21. New weeks start on Monday and end on Friday. No data will ever have a date of a Saturday or Sunday. I have colored the cells currently to indicate what the number should be. There is 4 alternating colors now. The color coding is just for reference and will not be used going forward. Data will be entered weekly indefinately.

View 3 Replies View Related

If Statements: Option Of Between 3 Weeks And 6 Weeks

Sep 7, 2009

i currently have a what if statement

=IF(B2>=NOW()-42,"< 6 wks","> 6 wks")

I need it to also give an option of between 3 wks and 6 weeks

Therefore all options are

3-6 WKs
< 3 WKs
> 6 WKs

I have mastered 2 but now need a 3rd.

View 7 Replies View Related

Formula To Calculate Based On Start Month Number And Duration Number Of Months

Mar 14, 2014

I need a formula that will calucalte the monthly total based on the following conditions:

Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.

Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx

View 3 Replies View Related

Create Calculator To Show Start And End Date Based On Specific Date Provided

Jan 12, 2014

I am trying to create a calculator which will show start date and end date based on the specific date provided.

EX: column A has January 1, 2013, column B has Wednesday, Column C should have a start date which supposed to be 4 days ago (December 28).

View 6 Replies View Related







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