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
Why does Weeknum say there are ONLY 2 days in the first week of 2011? Aren't there 7 days in a week?
=Weeknum("1-1-2011",2) = Week 1
=Weeknum("1-2-2011",2) = Week 1
=Weeknum("1-3-2011",2) = Week 2
(I'm assuming the first day of the week is a Monday, but I still get wacky results if I start the weeks on a Sunday.)
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: ...
View 9 Replies View RelatedMy 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.
Here is a copy of my formulas:
=IF(AND(F8)=0,""*(IF(AND(F8)
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)
i am looking to do a table which shows time started, time finished and then a total for hours that day, then that week.
Start 08:00
End 16:00
Total 8 hrs.
How can I get the total to display as 8 hrs? not 08:00? When I change the format to "number" it shows 0.33?
Need to calculate the mean hours by the day of the week. We only want to know the mean if it occured after a certain time and if it was on the same date then that would be totalled for that particular week day. I have included a small sample of the file I am working on.
View 4 Replies View RelatedI'm trying to create a time sheet to calculate how many hours worked in a week, Once it reaches 40 hours, The excess over 40 hours goes into a "overtime" cell. The "40" hours remain in the regular hours cell.
Attachment 267704
My overtime pays is anything exceed over 8 hours per day or over 40 hours per week. Right now I can only calculate overtime by either over 8 hr/day or over 40 hr/ week. I need a way to combine both.
View 9 Replies View RelatedI am trying to figure out a way to count the minutes used from 8am-5pm Monday-Friday. I have this years worth of data. I would like to do it by month and by week and by day. I use office 08 for the mac and its my understanding that it doesn't have VBA. I would also like to be able to figure out if on a certain date a employee made over x amount of phone calls in a day. But have several employees. I have columns that are labeled date, employee, minutes used.
View 2 Replies View RelatedI 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.
View 8 Replies View RelatedEssentially I have the following:
Sheet 1 (raw data)
column a = day
column b = weeknum
column c = Employee Name (5 names)
Sheet 2 (sheet I would like to create graphs)
I need to sum total records of each employee per week. For example:
column a2:a6 are each possible employee name
column b1:h1 are weeknums 1-7
I've tried countif but I'm only able to make that work if there's a helper column.
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 have a column A with dates eg: 2012/04/02 and I want to add a value into column B 120 but on a Saturday it must return value 80. So Monday to Friday must return 120 and Saturdays must return 80.
View 2 Replies View RelatedI'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
I have a worksheet that lasts for a year that is something look like this:
Date Name
1/1 John
2/1 Erwin
3/1 Robert
4/1 John
16/1 Erwin
17/1 Erwin
17/1 John
In my report worksheet, I need a formula to count everyone's attendance from their participation dates based on week
Name Week1 Week3
John 2 1
Erwin 1 2
Robert 1 0
I am creating a load file for software from Excel. Based on our planning calendar and delivery time, I need to calculate the Delivery day.
Excel File as follows:
Planned Delivery Time Planned Order Day Planned Delivery Day
8 days Thursday (Need a formula that says 8 days from Thursday would be Friday)
13 days Tuesday (Need a formula that says 13 days from Tuesday would be Monday)
I have attempted with simple formulas adding time, complex formulas, and cannot find a simple solution that works for days of the week.
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"......................................
I have a formula
=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.
View 3 Replies View RelatedIn 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.
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 ;-)
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)
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 Relatedon 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
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.