Name File Based On Date & Week Number

Aug 25, 2007

on a macro i use to open, update a file and then save it in an archive.

Opening and updating the file is no problem, but i want to save under a dynamic name in a folder structure. This is a reoccurring task, and this way I update the same file each period but save a copy with the current data in an archive under a different name in the right place of a directory (archive).

My idea is to have a hidden cell in this workbook, where I can have the name calculated by simpe excel-formula, i.e. ="Filename_"&WEEK(TODAY())&"_"&YEAR(TODAY())&".xls".

The file should go automatically into an archive-directory, lets say C:data....archive2007 (2008, 2009, etc)
I want to add the last folder to my filename, so my macro knows the first part of the path and has to go look up the actual name it gets in order to be saved.

So I end up with a cell containting the filename: 2007Filename_35_2007.xls

Now I only need the macro which looks up this name, adds it to the hard programmed path:
C:data...archive
so that the file gets saved under as: C:data....archive2007Filename_35_2007.xls

this is how i start:

Workbooks.Open Filename:= _
"C:data....Filename.xls"
' here the file is updated...
Windows("Filename.xls").Activate
ActiveWorkbook.SaveAs Filename:= _
"C:data....archive'(how do i do this?)'", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

View 3 Replies


ADVERTISEMENT

How To Calculate The Week Number Of The Month Based On A Date

Dec 30, 2013

I would like to calculate the week number of the month based on a date.

Now my days would only include working weeks (Monday - Friday).

Supposed the date is 12/31/2012:

M
31-Dec
T
1-Jan
W
2-Jan
TH
3-Jan
F
4-Jan

Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:

M
28-Jan
T
29-Jan
W
30-Jan
TH
31-Jan
F
1-Feb

It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:

M
29-Apr
T
30-Apr
W
1-May
TH
2-May
F
3-May

It will be considered as Week 1 of May since it occupies only 2 days of the working week.

Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.

View 4 Replies View Related

Formula To Identify Week Number Based On Date Ranges And Add Values

Feb 18, 2014

I have the following data:

Column A = Date
Column B = Reservations made per day

For ex:

A B
1 3/1/2011 5
2 4/5/2011 10
3 3/8/2011 15

Then I have a look up table where based on the date ranges it assigns a week number.

WeekDATE Range 1Date Range 2
718-Feb-1124-Feb-11
825-Feb-1103-Mar-11
904-Mar-1110-Mar-11
1011-Mar-1117-Mar-11
1118-Mar-1124-Mar-11
1225-Mar-1131-Mar-11
1301-Apr-1107-Apr-11
1408-Apr-1114-Apr-11
1515-Apr-1121-Apr-11
1622-Apr-1128-Apr-11

I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.

View 11 Replies View Related

Formatting Date: Add The Month And The Text "Week" Before The Week Number

Nov 24, 2009

I have a column where I am convering the Date into a Fiscal week number.

For example 10/6/2009 is Work week 41
Now I want to show October Week 41

I need to add the month and the text "Week" before the week number. what is the formula I use.

View 3 Replies View Related

Show Week Number Of Today Date From Starting Date

Jun 27, 2014

is it possible to display the week number of todays date (today()) from a physically entered start date (which would obviously be week one), the start date would be november 4th 2013.

View 3 Replies View Related

Save A File With Variable Week Number?

Aug 13, 2012

I am still learning VBA and am wanting to know can you save a file with a variable week number?

e.g file name = 657 13082012 CP

The Following week would need to be 658 20082012 CP

View 1 Replies View Related

Week Number For Date Relative To Date Range

Mar 17, 2008

Is there a UDF that can determine the number of weeks for a date range specific that is not relative to the week number for the year but for the date range itself. i am aware of the weeknum function but this is for week number relative to the year. eg. date range 01/03/2008 - 31/05/2008 has approx 12 weeks and 14/05/2008 will be week number 10 for the range.

View 4 Replies View Related

Week Number Of Date

Sep 5, 2006

I'm using excel 2003 and I searching for a small code to automaticly generate the begin- and end- date of a week (from monday till sunday) the only variable that I wanna give is the Weeknumber. So if I write a weeknumber in cell(a,1). I want the begin-date (monday) in cell (b,1) and I want the end-date (sunday) in cell (c,1).

for example:.....

View 4 Replies View Related

Get Week Number From Date

Feb 15, 2008

Is there a function in Excel we can use identify a certain date to be the week no. ?

For example, 15 Feb 08 is (part of) Week 7 of 2008

View 8 Replies View Related

Date To Week Number & Year

Oct 9, 2007

I've done a search on here to find out how to convert a date to a week number & found this: - =WEEKNUM(A1) which works fine, But I also want the result to display the year.

So 08/10/07 becomes WK40-07

I can't see how to do it!

View 5 Replies View Related

Format Date Cell With Week Number

Jan 8, 2009

just wondering if its possible to format a cell to display date and week number and if so how to go about it

eg

04/01/09 week 1

View 7 Replies View Related

Convert Fiscal Date To Corresponding Week Number

Nov 12, 2009

I have a spreadsheet that I use to convert a purchase order ship date from the actual date to the corresponding week it falls out on. The fiscal year always starts on February 1 regardless of the day of the week. The problem i am encountering is when the year changes. As soon as I enter 01/01/2010, the response I get is -4, where as 12/31/2009 is 48.

I am using the following formula that I found somewhere, where R2 = 02/01/2009 (02/01/2009 falls out on a Sunday). =INT((R2-DATE(YEAR(R2),2,1)-WEEKDAY(R2,1))/7)+2. I need to make the formula "not care about" the day of the week.

View 3 Replies View Related

Vba To Find The Month And Week Number From Given Date

Apr 22, 2009

In the attch file i have the date coulumn from this date column i need to calulate the month & week no. (like WEEK1,WEEK2..)

The Week ( Monday to sunday) which need to be calculated is the week no. in the given month

like for month of April the week1 is print in the week column for 6april to 12 april date and Week2 print for 13 April to 19 april

View 14 Replies View Related

How To Calculate Week Number That A Date Falls In

Aug 6, 2013

Of the 52 weeks in a year, I want to know the week number that a date falls in, e.g. date - 05/08/2013 falls in Week No. 32. What's the formula to get this answer?

View 2 Replies View Related

Get Date From Week Number And Year ( Fiscal Calendar)

Jan 22, 2013

I'm having a data only pull week number and year. We are using Fiscal calendar starting in July. For example, A1 = Week number and A2= Year. How to set up a formula to retrieve a date for this? If A1 = 2 , A2 = 2013, the date will be 07/14/2012. I want the date pull of on Saturday every week.

View 6 Replies View Related

Find Week Based On Date

Apr 17, 2014

I've got a column with multiple dates and a lookup sheet with the weeks (start date/end date). What I'm trying to do is :

1) check date
2) look it up in the lookup sheet (where it falls between start data/end date)
3) once i find where it falls, return the corresponding week name/number

I've seen a solution using the Median function but I'm not here it would work in my case. link here

I've attached a workbook : Date_Sample.xlsx‎

View 12 Replies View Related

Week Number Based On Year Start 01/10

Jan 9, 2008

My finacial year starts on the 1st of October of each year and ends 30th of September each year.

My week is from Monday to Saturday - though for calculation ease: lets say that my week is from Monday to Sunday.

I need to calculate the week numbers of a date based on the above two criteria.

I understand that I need to ascertain the day of the start date first i.e: did 01/10/07 fall on Monday, Tuesday etc. - In fact it was Monday!

so for my year 07/08:Week 1 was 01/10/07 to 07/10/07Week 2 was 08/10/07 to 14/10/07ETCSo in the attached worksheet - what formula can I use to populate column D - by using the data in column E?

I.e. All I want to do, is input date into E and B, C, D will be calculated automatically. It would be great of excel could populate A - aswell ;-)

View 11 Replies View Related

Calculate Friday Based On Week Number

Jan 9, 2012

I have a drop down box that chooses the week number of the year (This is based off of a series of data from another sheet).

I need some kind of formula that calculates the following Friday based on a week number. Say for this year (2012) The following Friday for "week 1" is 1/13/12.

(This is for payroll information and I'm trying to calculate the pay date based of of data from that week)

View 8 Replies View Related

Date Range (formula Show A Week Number In The 2nd Table )

May 6, 2009

I have two tables: the 1st table consists of date range (From and To) and week number while the other table has only dates.

Example:
1st Table
FROM TO WK
3/27/2009 4/2/200914
4/3/2009 4/9/200915
4/10/2009 4/16/200916
4/17/2009 4/23/200917
4/24/2009 4/30/200918

2nd Table
DATE
03/28/2009
04/11/2009
04/26/2009

Need simple formula that would show a wk number in the 2nd table (2nd column)? I.e 03/28/2009 has wk no. 14, etc.

View 5 Replies View Related

Update Day Of Week And Date Based On Month

Sep 3, 2007

I'm attempting to force excel to auto update the day of the week, and the date in a spreadsheet. The date isn't as important, since it can be hard coded. The only problem there is some months have 31 days, some 30, and another with 28. I've uploaded an image of the spreadsheet, and you can see in field A1 the date/year is input. I'm wanting to find a way to force the days/dates in fields 2E and 3E to update based on the month.

View 9 Replies View Related

Find Last 12 Values Based On Week Number / Year

Jul 9, 2014

I am trying to fill a table of the last 12 values for the purposes of creating dynamic charts. I remember last time i used named ranges, offsets etc etc but been too long to remember how.

Ive attached a worksheet to explain it better.

I should probably mention, I want to be able to change cells C1 and C2 to update the values. Everything else wil be rather static.

Attached File : Test.xlsx‎

View 5 Replies View Related

Offset From Cell Based On Inputed Week Number

May 20, 2008

I want to have a variable range.

ie. This value M4 needs to change based on the week.

Range("M4").Select

If it's week 1, then it can be say M4, but week two will need data to go to N4.

I have found out how to get the week number from the user. ie.

' Select Week Number
Dim NumSheets As Integer
Dim Prompt As String
Dim Caption As String
Dim DefValue As Integer

Prompt = "What week do you want to get data for?"
Caption = "Week Number"
DefValue = 1
NumSheets = Val(InputBox(Prompt, Caption, DefValue))
If NumSheets > 52 Then MsgBox "Week Number too high"
If NumSheets < 1 Then MsgBox "Week Number too low"

I tried then linking this value saved as NumSheets by:

Dim Rng As Range

If NumSheets = 1 Then Rng = Range("D3")
If NumSheets = 2 Then Rng = Range("E3")

View 3 Replies View Related

Calculating Number Late Last Week From Multiple Dates / Date Sets

Jul 2, 2014

I am putting together a simple table to display current week's data vs previous weeks. The current week's data is drawn from a status chart which changes frequently. The constant change is fine for 'Current' as I only want the current data displayed.

The problem I am having is calculating the number of late jobs that existed during the previous week.

The status log has a due date which is compared to the current date to determine 'on time' status for the current week.
Due dates are reissued regularly so I can't use

=COUNTIF(RANGE,WEEKNUM(NOW()-1)) to return data about last week from my status chart.

I have available a 'Movement Log' (in the workbook but a separate worksheet) which tracks the changes in the due date field, but I'm not sure how to integrate that data to calculate the # of jobs that were running late from the last week.

My thought is that I need to perform a count of the # of late based on a comparison of 'due date' to 'date of the last day of last week' with a way to insert the "old due date" from the movement log to replace what is shown in the status log if necessary.

Movement Log.JPG

Status Chart.JPG

View 1 Replies View Related

Calculate Time Elapsed And Compute Day Of Week Based On Date

May 27, 2014

1. Calculate the time that has elapsed between 2 times in both hours:min (hhmm) and total mins (mm)

2. Compute the day of the week (mon-fri) a particular date fell on. I really only need to know if the date fell on a weekday or weekend.
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= M-F

2=S-S

3. How to write an If statement that assign a value to time based off this chart:
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= AM (7-1459)

2=PM (15-2259)

3= MN (23-659)

View 9 Replies View Related

Auto Open Macro To Find Correct Week Tab & Day/date In 52 Week Worksheets

Jan 19, 2010

I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).

Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................

View 9 Replies View Related

Week Number Caluclating Hours For Week

Mar 6, 2010

I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel
i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.

The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2

2 problems
Having many multiple day / date entries are the same date x 7 days Monday –Sunday
(Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time

When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate
Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel

i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals
i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007

how do i calculate weekly hours by date and weeknum ?

Total Work per day

Total Travel per day

Daily....................

View 9 Replies View Related

Macro To Download Csv File From A Dynamic Date Based Url To A File Location

Feb 23, 2014

I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....

This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..

View 1 Replies View Related

Open Excel File Based On Date In File Name That Is From One Day Before

Apr 24, 2014

I need to open an excel file based on a date mentioned in the file name, but minus 1 day. The file name is composed with Statistics_date_time.xls

Example: statistics_20140423_142754.xls

Once I need to open the file (only in the mornings), it has to be the one from the previous day to see the yesterdays performance. How can I create a macro that will look at the date in the file and take the one from yesterday?

This will be a part of a macro I'm making. The rest of the macro will just format the file and combine a few ones from different departments.

View 4 Replies View Related

Count Unique Instances Of Date While Filtering Based Upon Date And ID Number

Aug 14, 2014

I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.

I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.

I've attached an example work book and I would like the result in H2.

Example Workbook.xlsx‎

View 5 Replies View Related

Find Corresponding Date Range Based On Single Date And ID Number

Mar 28, 2014

I'm trying to combine the hours that a employee worked on a single date, with one of multiple time periods that exist for that employee.

I have two sets of data.
Set 1 (hours)
Employee number, date, hours
12345, 1-2-2014, 6
12345, 1-3-2014, 8
12345, 1-10-2014, 8

Set 2 (periods)
Employee number, start date, end date
12345, 1-1-2014, 4-1-2014
12345, 6-1-2014, 1-2-2014

What I'd like to do is to add the start and end date of Set 2 to Set 1 for every row in Set 1

In above example the result should be like this.
12345, 1-2-2014, 6, 1-1-2014, 4-1-2014
12345, 1-2-2014, 8, 1-1-2014, 4-1-2014
12345, 1-10-2014, 8, 6-1-2014, 1-2-2014

View 13 Replies View Related







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