List All Days For The Week For The Year

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


ADVERTISEMENT

Year,month,week,days Calculation

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

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 View Related

Generating List Of Week Working Days And Identifying Holidays

Oct 28, 2009

From another thread I got this formula placed on B5:
=NETWORKDAYS(EOMONTH(B4,-1)+1,EOMONTH(B4,0),Holidays)

his generates the number of working days for that month (regardless of the date on B4) that exclude Holidays listed under the range "Holidays".

I also got this code (Credits to XXXX)

View 4 Replies View Related

UDF To Output Tax Year Week Number

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

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

Recognize A Series Of Dates As A Particular Week In The Year

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

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

Calendar From Formula :: Nth Day Of Week For A Month And Year

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

Auto Set Days Of Week

Oct 4, 2012

Any 'date' or 'day' formula that will automatically insert days of the week in a column? I am attaching a sample.

I want to be able to use a drop-down menu to insert a day of the week (e.g.: Sunday) in cell B2 and have the succeeding days (Mon, Tues,Wed, Thurs, Fri, Sat) populate cells C2~H2 in sequence.....

View 9 Replies View Related

Average Of First Three Days Of The Week?

Feb 9, 2014

I have following data

Column A Name of the company
Column B Date
Column C Day
Column D Week of the Year
Column E High Price
Column F Low Price

My requirement is I need the average high price for the first three days of the week i.e Mon, Tue, Wed in Column I and average Low Price for the remaining days is Thu, Fri, Sat , Sun in Column J.

If in some case any day is missing then average high / Low price for the next two days is to be calculated.

View 6 Replies View Related

Delete Days Of Week

Aug 16, 2013

I had a column of text and numbers in col A; day of the week and date (taken from a web scrape, so have no control over how they are imported), such as in the following example. Additionally i have put a column of one's beside it in col B, and then summed them in col C.

A ..........................................................B .....................C
Friday 16/08/20131
Thursday 15/08/20131
Wednesday 14/08/20131
Tuesday 13/08/20131
Monday 12/08/20131
Sunday 11/08/20131
Saturday 10/08/20131
Friday 09/08/20131
Thursday 08/08/20131
Wednesday 07/08/20131
Tuesday 06/08/20131
Monday 05/08/20131
Sunday 04/08/20131
Saturday 03/08/20131sum=14

If i parse the text from the numbers, and then use a filter to remove Saturdays and Sundays, the sum total of col B will still remain the same. What i need is a method to totally delete or neglect Saturdays and Sundays so that the sum would equal 10 for example. Note that the data is updated daily, so in A1 today will be Friday 16/08/2013, but A1 tomorrow will be Saturday 17/08/2013. Also note that i have used 1's in my example, but that is just for explanation purposes, the actual data is varied.

I have just spent the last two days making this spreadsheet only to realize that weekend data is totally messing up my calculations.

View 4 Replies View Related

Filter Days Of The Week

Oct 18, 2007

How could I filter days of the week.

Although the date formatting contains the date, ie Thursday, 18 October 2007, when I try to use a contains filter it wont recognise the text of the day I want to filter.

View 9 Replies View Related

Production Days In A Week

Sep 10, 2008

I have a production schedule with start and finish dates for each project. To the right of this I have a matrix with "week starting" dates. What I am looking for is a formula that will indicate how many days in each week the project will take. Below are the cells filled in correctly. However, I did this manually. Any idea of a formula? I'm pretty new to this.

WEEK STARTINGSTARTFINISHUNITS PER DAY9/1/089/8/089/15/089/22/089/29/089/10/089/24/0813.4503530

View 9 Replies View Related

Counting Days Of The Week

Jun 5, 2009

I need to be able to count the number of each day that has passed so far this month. For example, so far this month the table would look like:

Mondays:1
Tuesdays:1
Wednesdays:1
Thursdays:1
Fridays:0
Saturdays:0
Sundays:0

I don't want the count to include the current day. My data is in a workbook called "Stock Today.xls" on a sheet called "Sales" in column S.

View 9 Replies View Related

Weeknum Says There Only 2 Days In Week

Jun 11, 2009

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.)

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

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

Auto Fill Days Of The Week In A Row?

Dec 4, 2013

I am trying to fill a row with abbreviated days of the week. example type Wed in cell A1 and cells B1 through Z1 change to the appropriate day Thu, Fri, Sat etc. I know how to do it with the fill off the menu but want to be able to change Wed to Thu and the same cells will change.

View 7 Replies View Related

Days Of Month And Day Of Week Automatically Set?

Feb 25, 2013

I currently am trying to refine some spreadsheets at work (hospital setting). The type of files im working with are medication sheets where on the left it states the medication and to the right of it, the cells have the days of the month(1-31) but I need them to change depending on the day they come into our facility. Above the numbers i would also like it to say the day of week with the first initial (M, T, W, T, F, S, S) in the cells are the top. It is something that we have to make for each day it it gets really annoying and is a waste of time moving the dates over for every day. find a way where I can open the file and the numbers and letters are all in the right place without having to change it for the day that the patients are coming in.

View 3 Replies View Related

Show Days Of Week In Excel

May 24, 2013

I have created a excel file which stores attendance for my employees.

The format is like

Select month in one cell
select year in other cell

And from cell e8 to ai8 i have nos (1-31) represent no of days in a month:

a8 - 1
b8 - 2
c8 - 3
d8 - 4 like wise till ai8

Now I want a calender where user need to select the YEAR, and then MONTH and based on the above selection both days as well as date (not the month and year) should be displayed.

I am currently using this formula "=IF(ISERROR(--(COLUMNS($E8:$E$8) & "-"&$C$2)),"",COLUMNS($E8:$E$8))" for date and your vb code to hide the cells depending on days of months.

I need something like this

sun mon tue wed thur fri sat sun mon
1 2 3 4 5 6 7 8 9

View 3 Replies View Related

Number Of Days Remaining Until End Of Week?

Jul 31, 2013

I am trying to get a formula that will give me the number of days remaining in the week. I have a formaula for number of days until the end of the month and number of days until the end of the quarter, but just need the week.

So if the formula was run today it would show 4 as the result.

View 9 Replies View Related

Using Days Of Week As Criteria For Filtering?

Oct 20, 2013

Row H contains a date.

This date "example... 10/14/2013" Will always be a MONDAY

I am trying to figure out the code to filter out any Monday that is not of the current (changing) week.

View 5 Replies View Related

Build Array With Days Of The Week

Aug 7, 2008

This line will give me the current weeknumber

wkNumber = DatePart("ww", Now(), vbMonday, vbFirstFourDays)
But also i would like to have a dynamic array that will give me the dates of this week
Currently i do that using vlookup in a table stored in my personal.xls but that is no longer possible as i have to distribute my macro and i no longer have that possibility.

View 9 Replies View Related

Calculate Days In A (06 Day)Working Week

Dec 27, 2008

to calculate no.s of workdays between two given dates,

1.We work 06 days a week (so only Sunday is OFF)

2.What if I have some holiday between the given dates + Sunday OFF

View 9 Replies View Related

Format Color For Some Days Of Week

Feb 16, 2007

I have a Timesheet where I am trying to get Saturday and Sunday to auto fill with a certain color whenever the month is changed. The spreadsheet is setup so that if the month is changed in a specified cell, then the dates of that month auto fill down a specified column. I would like Saturday's and Sunday's to be a certain color without having to manually color fill them every time the month is changed.
Is there a formula or macro for this. I have tried setting up conditional formatting but I don't know how to get the color to follow Sat&Sun. I have attached the spreadsheet.

View 4 Replies View Related

Format Date To Separate Different Days Of Week

Apr 29, 2014

I am very new to Excel. The date is in column B as Tuesday, 29 April 2014, but I need to format the spreadsheet in such a way that every day of the week is a different colour e.g. Tuesdays are Blue, Wednesdays will automatically turn pink etc. how will I do that?

View 2 Replies View Related

Setting A Formula To Relating To Days Of The Week

Oct 6, 2009

Sheet1!A1 = Sat

Sheet2! A2 = I need this to equal Sun....

I tried =sheet1!A1+1 but got #value

View 9 Replies View Related

COUNTA And Sum: Counts How Many Week Days There Are In A Particular Month

Feb 4, 2010

I have using the following Formula: =COUNTA(A3:A7,A10:A14,A17:A21,A24:A28,A31:A35)
Basically is counts how many week days there are in a particular month. Now I have a cell (B47) that counts how many Bank Holidays are in that month. I am having trouble using the formula then minus B47. I must be missing something really simple.

View 3 Replies View Related

Calculate Number Of Week And Days Between Two Dates?

Sep 28, 2011

Any example of counting the # weeks/days between two dates?

View 1 Replies View Related







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