Creating A Customized Accumulative Code Week (modified WEEKNUM) Formula
Jul 4, 2008
My company uses a customized accumulative code week system, as in the year 2000 to today is about 442 weeks with the year 2008 starting around week 416.
Here is how my formula works(or how I would like it to work), if the date (in cell F8) is blank, nothing is displayed, if there is a date (format 2008-07-03) and it falls within 2008, WEEKNUM+416(416 is a fixed reference in J4), if the date falls within 2009, WEEKNUM+416(J4)+52, if the date falls within 2010, WEEKNUM+416(J4)+104. I only need it to go from 2008 to 2010.
This way J4 is a fixed code week reference where 416 is added to each week number, but it does not work for years 2009 and 2010 as it does not add the weeks from the starting reference.
I am currently using the =Weeknum(A1,15), so I have a Fri to Thu week. My issue is that I need anything that is done on Thursday after 3:00pm to be identified as the following week.
For example 04/03/2014 2:00pm = week 14 04/03/2014 3:01pm = week 15
We have a fiscal calendar which starts Oct 1. I would like to display the proper week numbers. I worked out a formula which seems to work (except for week 53) but it would be better if I didn't have to rely on other users having the Analysis Toolpak installed. My date is located in '3930!I4' and this is the formula that works with the toolpak: ...
Is there a shortcut in doing this instead of copy pasting it?
I have a record just like this with about 572+ rows and 13 repeating items with that 13 items having their own column so copy pasting is really a tedious task. Kindly please help me solve this?
I'm using the following code to create a customized userform, and it works perfectly on my computer, as I've enabled all macro settings.
Code: Function GetOption(OpArray, Default, Title) Dim TempForm 'As VBComponent Dim NewCheckBox As MSForms.CheckBox Dim NewLabel As MSForms.Label Dim NewCommandButton1 As MSForms.CommandButton
[Code] .......
However when my coworkers need to use the code it creates an error, as the VBA project is unsafe.
Now my question is, can you write some code that enables all macro settings temporarily, in such a way that my coworkers can use this code?
In column A I have the following formula =CONCATENATE((YEAR(B1)), " - ", (WEEKNUM(B1,1)))
In column B I have a date.
I need column a to display in YYYY-MM format.
This works great from about mid-March thru the end of the year when the week number is 10 or greater. As an example when the date is 1/31/2012, column A displays as 2012 - 5. For sorting purposes, I need it to display as 2012 - 05.
I copied this snippet from the net and it has been working like a charm. I would like to add DateLastModified to it, but I can't get it to work, I'm still kind of new to more complex VBA codes. Here is the code...
Code: Private Sub CommandButton1_Click()
Dim cell As Range Dim selcell As Range Dim Value As String Dim Folder As Variant Dim a As Long
I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
I currently have a code that searches a folder, opens a spreadsheet, and then copies over the "Status" tab to the Master document.
New spreadsheets will be created a couple times a week, so I would like the code to be able to search this folder for the most recently modified document and then open THAT document.
So, if my folder has..
Report 10May11 Report 05May11 Report 17May11
Then, I would want a code to search in this folder for the most recently updated document (in this case "Report 17May11.xlsx) and then go through the step I have above where it copies over the various tab.
Basically, this code would I guess replace the example code I have below.
I am trying to get a sheet to automatically calculate week numbers from a given date (start) and the current date, the problem I am having is I only want to cycle 18 weeks not 52.so weeknum wont work
ie 10 weeks after start date would return Week 10 18 weeks after start date would return Week 18 19 weeks after start date would return Week 1 25 weeks after star date would return Week 6 37 weeks after start date would return Week 1 etc...
I needed a code that would input the current date and time in the cells in column N whenever changes were made to any cells in the row from columns A to M. For example, if I change a name in cell 6D, then cell 6N would automatically change to the current date and time.
I found a useful code on a forum (maybe here, don't know for certain) and modified it to suit my needs (see below). I am however now getting a debugging error suggesting that the second line that reads "Private Sub Worksheet_Calculate()" is causing an error.
I am trying to monitor the status of a cell on another sheet and autopopulate a cell depending on that information. The formula works well until I give the spreadsheet to a 3rd party and the formula ranges change after they paste new data to the source sheet. I have tried locking and password the formula cells but they change range regardless!
Here is the formula from the first cell.
[Code] ......
It scans for a number in an adjacent cell. If the number is present on the sheet 'Test', it autopopulates the cell with a string from the source sheet. The cell remains blank until there is data present.
If I cut data from row 6 and paste it to row 17, the formula cahnges itself to:
[Code] ........
How I can lock down this formula so that the ranges remain the same i.e. $AT$6:$AT$26, despite changes on the source sheet? I have tried F4 to toggle relative and absolute references and this has made no difference.
I got a file from my boss that is linked to an online table. This table contains customer contacts infos such as account number, activation date & time, etc, etc.. (1 contact = 1 line)
Now (for "quick filtering reasons" he said), my boss "needs" to have also in the sheet the week number for every contact we had..(as well as Month & Year) and as the list grows up everyday with new entries, I thought I could use VBA. so I wrote a little something that uses the date&time column to have the wanted infos added on every line :
Formula below returns Year Week format. e.g. 11/06/08 entered in cell C13 returns 0845
=VALUE(RIGHT(YEAR(C13),2)&TEXT(WEEKNUM(C13),"00")) however entering 12/29/08 returns 0853, but management wants it as 0901. 01/05/09 returns 0902. Is this how Weeknum calculates, do I need to write my formula to if 53 then... or am I missing something with Weeknum?
Does VB have the WEEKNUM feature ?, I can use WEEKNUM in an Excel sheet ok, but it is not recognised in VB ... I have installed both the Analysis tool packs, the one for VB as well.
How come the WEEKNUM formula in the attached file does not work?
The cell gives me "#Name?", and I have no idea why.
I noticed one thing though, which is that WEEKNUM function is described when I look in "Help", but the function is not in my "Insert Function"-wizard, so perhaps the function is not there at all, which is very odd.
I am trying to get the week from a given data (from a cell that has been correctly formatted with date). =YEAR works, =MONTH works, =WEEKNUM does not work. I am using Excel 2003 and I have tried in both my workplace and at home...
I.e., I have a date in C2 (18-05-08), and =YEAR(C2) returns 2008, =MONTH(C2) returns 5, while =WEEKNUM(C2) returns #NAME, and =WEEKNUM(C2,2) does not work at all...
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).
=IF(I1239="","",IF(OR(H1239="Induction",H1239="One to One"),WEEKNUM(I1239)-WEEKNUM(K1239)+1,""))
Which works fine an counts the weeks from the input date I1239 from the initial start date K1239, however if the input date runs into next year 2014 the result ends up as a minus figure eg -47 instead of the next sequential figure which would be say 5 or 6
I have some formulas in my workbook that use the weeknum function from the analysis toolpak. I have the analysis toolpak installed, yet every time I save the file and reopen it, the formulas with weeknum are gone, with #N/A in its place as the formula. Sometimes the results of the formula from the last time remain (the cells contain the number of the week, but without the underlying formula), sometimes not. All the other formulas are fine. The formulas are simple, like =WEEKNUM(C2)
I have been trying to save as a 97-2003 .xls file. Today I'm trying to save as .xlsx, but I have to rebuild the formulas first, so I don't know how that's going to turn out.
I need to use weeknum() for a number of situations in a project I'm working on. But when I use =WEEKNUM(DATE(2010;1;31)) it returns 6, instead of 4 as it should. Using it on today's date returns correct number.
what is the equivalent command to WEEKNUM if I want to properly calculate Week # of Month?
For example (Sunday being the first day of the week): January 5th 2008 = Week 1 of January January 6th 2008 = Week 2 of January February 2nd 2008 = Week 1 of February February 3rd 2008 = Week 2 of February
WEEKNUM perfectly calculates this, but it is applicable for the whole year.
Is there a way to have customized cell backgrounds that are more complex that Excel's preset colors and patterns?. For instance, I have a merged cell (it has to stay merged) with a value that is located at the bottom-center of the merged cell. I want to have either a fading gradient (the top of the cell is white fading to a blue bottom towards the value), or just have the bottom portion of the merged cell colored. I have Excel 2003 so I don't know if any other version has this feature.
