# Convert Date To Year/week Of Year/day Of Week

Apr 6, 2007
Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)

View 9 Replies
ADVERTISEMENT
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
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
May 4, 2006

I can retrieve the weeknum of any particular date using =WEEKNUM(x) or =TRUNC(((x-DATE(YEAR(x),1,0))+6)/7)

But I'm looking for a UDF to output the tax year week number where the tax year always starts on 6th April. The std weeknum for 6th of april pretty much varies around week 13 but as it varies I don't ever get a strictly true result by deducting 13 from WEEKNUM().

View 4 Replies
View Related
Oct 11, 2009

i have the year in cell A1 (Just The Year)

what i was looking for is a formula that will list all the days of the week..

IE if i pick Monday form a drop down list than it would like all the mondays for all twelve (12) Months

so Jan Column would say

5

12

19

26

31

and so on for each month than if i picked a different day it would do the same

Using Microsoft Excel 2003 On Windows Xp Home Edition

View 9 Replies
View Related
Feb 27, 2009

DATE function won't return TODAY()'s year in the "year" slot.

Is there a way convert, for example, 2/8/1963 to 2/8/2009 without using Concatenate?

View 9 Replies
View Related
Nov 12, 2008

My intentions are for Excel to recognize a series of dates as a particular week in the year. For example: 12/28/2008 thru 1/3/2009 equals Week 1, 1/4/2009 thru 1/10/09 equals Week 2.

I cannot for the life of me calculate a working formula.

View 7 Replies
View Related
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
Oct 12, 2008

I'm using this formula from

[url]

Nth Day Of Week For A Month And Year

This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+

(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.

I would like to be able to change the year and month in A2 and B2 and have the calendar change.

I will be inserting rows between the weeks to return appts, if I can get this part working.

I could make a new tab for each month, but I thought I would give this a try....

View 9 Replies
View Related
Aug 3, 2006

What I am trying to to is calculate the number of Years, Months, Weeks, and Days from one date to another. So far I can calculate years and months accuretly but I'm having trouble with the days and can't seem to figure out how to do the weeks.

I'm using the formula: =DATEDIF(B1,B2,"y")&" Year(s), "&MOD(DATEDIF(B1,B2,"m"),12)&" Month(s), and "&(MOD(DATEDIF(B1,B2,"d"),365))&" day(s)"

B1 is the current days date

B2 is the entered date

What I am getting when I enter the date 9/14/09 with the current date (8/3/06) is 3 years, 1 month and 43 days. when it shoud only be 3 years 1 month and 11 days. Any date I enter the days are not right. I can't seem to figure out what I am doing wrong. Also I'd like to get the weeks to come up also. Such as Start date 8/3/06 Entered date 10/19/09. What I want to see: 3 year(s), 2 month(s), 2 week(s), 2 day(s).

View 3 Replies
View Related
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
Apr 7, 2014

I want to create a dynamic line graph using week and year numbers stated in another sheet.

e.g.

Start Year - 2012

End Year - 2014

Start Week - 3

End Week - 12

The top 2 rows above my graph data are as below:

Year - 2012 2012 2012

Week - 5 6 7 etc.

This works fine if the start and end year are the same but if it's greater than one year, it doesn't recognise that.

View 7 Replies
View Related
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
Jul 24, 2009

I have a date which is recorded in the mm/dd/yyyy format, and in the adjacent column I would like to determine which week in the month this date is, from 1 to 4. So for example if the date was 07/02/2009, the week would be 1. For simplicity's sake I figued I would just have days 1-7 as week1, 8-14 as week 2, 15 - 21 as week three, and 22 on up as week 4. If there is a more accurate way of doing this, say that would be able to sart counting from the first full week of the month, then I would be very interested, but that seems unreasonable.

View 4 Replies
View Related
Mar 11, 2009

I have a form that users input the date (not just todays date) I want another cell to take that date and input the corresponding day of week.

example:

user inputs 03/10/09 into cell h17 I want cell A12 to automatically have the corresponding day of week (wednessday)

View 5 Replies
View Related
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
Jul 20, 2012

This might be a simple date transformation formula that I need

Column A has numbers like: 200517, 200530, 200544, 201036, 201043, etc

I'd like to get a formula in Col B in corresponding rows that would show date as dd/mm/yy

I know the numbers above are yyyyww = yyyy is year, and ww is the week of that year.

So the formula would have to convert that particular week to the day and month. I understand that are 7 days in a week, but if the formula would reflect Monday of that week, it would suffice.

View 2 Replies
View Related
May 14, 2009

I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)

View 3 Replies
View Related
Sep 18, 2012

Trying to take for example, 9/25/2012 and write a formula to make the resulting cell read 2H2012. Or 4/15/2012 and have the resulting cell read 1H2012.

View 5 Replies
View Related
Oct 13, 2008

i have the following table of information

Year DOB

7 01.09.96 -31.08.97

8 01.09.95 -31.08.96

9 01.09.94 -31.08.95

10 01.09.93 -31.08.94

11 01.09.92 -31.08.93

and a list of dates i need to look up the date to see which year it falls in and return the year

View 9 Replies
View Related
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
Oct 15, 2007

I have a sheet in my workbook with at least 180 small tables, there may be more.

I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.

For example:

If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.

View 9 Replies
View Related
Dec 11, 2013

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.

View 3 Replies
View Related
Aug 6, 2008

I have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.

Sub CheckForNewProjsRemovedProjects()

Dim MyCell As Range, oCell As Range, NewCell As Range

Dim Rng1 As Range

Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

' Columns("B:B").Select

Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _

"C1"), Unique:=True

For Each oCell In Rng1

For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)

If MyCell.Value = oCell.Value Then................

View 9 Replies
View Related
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
Sep 25, 2006

I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.

'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen

ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then

MsgBox ("You may not enter Data before the current Month")

Else '...... Run main code here

View 3 Replies
View Related
Dec 8, 2013

Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.

Month 2012 2013

Jan 300 100

Feb 275 100

Mar 400 150

April 650

May 454

June 800

July 500

Aug 375

Sep 525

Oct. 300

Nov 410

Dec 510

Sample file is attached : Comparison_Years.2011.xlsâ€Ž

View 4 Replies
View Related
Mar 8, 2014

I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.

The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.

Attached is an example : Budget Example.xlsxâ€Ž

View 4 Replies
View Related
Aug 20, 2013

How to get only MONTH' YEAR if there is a DATE-MONTH-YEAR in a cell?

A1: 27-July-2012

Answer D1:JULY' 2012

Pl note "' " is suffixed after JULY (the month).

View 4 Replies
View Related
Jun 16, 2014

I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.

I'm using weekday formula but no luck.

View 6 Replies
View Related