Date formulas #4

Hi all
What is the formula (is there a formula) to display the Week number?
Trying to set up a spreadsheet which runs from first to last day of each 
month but automatically fills in a 5 week rota.  
0
8/14/2005 8:01:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
382 Views

Similar Articles

[PageSpeed] 49

Sorchaainne Wrote:
> Hi all
> What is the formula (is there a formula) to display the Week number?
> Trying to set up a spreadsheet which runs from first to last day o
> each
> month but automatically fills in a 5 week rota.

Try WEEKNUM functio

--
Paul Sheppar
-----------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2478
View this thread: http://www.excelforum.com/showthread.php?threadid=39563

0
8/14/2005 8:11:49 AM
This page might be a help:
http://www.bygsoftware.com/examples/iso_dates.htm
This page is based on ISO 8601.

It includes the Excel formula and an online week-number calculator using
Microsoft's spreadsheet component.

-- 
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Sorchaainne" <Sorchaainne@discussions.microsoft.com> wrote in message
news:25F601E0-479A-4F5C-B010-F77152E18F92@microsoft.com...
> Hi all
> What is the formula (is there a formula) to display the Week number?
> Trying to set up a spreadsheet which runs from first to last day of each
> month but automatically fills in a 5 week rota.


0
ng20050514 (63)
8/14/2005 8:23:57 AM
There ios a WEEKNUM function, but it isn't that simple. See

http://www.rondebruin.nl/weeknumber.htm

and

ISO Date Representatation and Week
Numberinghttp://www.rondebruin.nl/isodate.htm
-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Sorchaainne" <Sorchaainne@discussions.microsoft.com> wrote in message
news:25F601E0-479A-4F5C-B010-F77152E18F92@microsoft.com...
> Hi all
> What is the formula (is there a formula) to display the Week number?
> Trying to set up a spreadsheet which runs from first to last day of each
> month but automatically fills in a 5 week rota.


0
bob.phillips1 (6510)
8/14/2005 9:14:36 AM
Reply:

Similar Artilces:

filter by date and action
I have a table with three columns: Name Date Action Fred Today this Fred Today this Fred Today that Wilma Today this I want to sort by name and by date, but only if 'this' was done more than once on the same day, and not if 'that' was done on the same day,They have to perform this more than once, and they cannot have performed that on the same day. If not in excel, mssql maybe? Thanks, Harry On Aug 18, 1:53=A0pm, Harry <hamac...@gmail.com> wrote: > I have a table with three co...

Calculate Years/Months Between Dates and then Average
We are using the formula =DATEDIF(A1,NOW(),"y") & "." & DATEDIF(A1,NOW(),"ym") to calculate the years and months from an employee hire date. Is there an easy way to average the years and months for a group of employees with the results of that formula? You could use: =AVERAGE(A1:A10*1) Array-entered, meaning press ctrl-shift-enter. But you could also just change your current formula to: =1*(DATEDIF(A1,NOW(),"y") & "." & DATEDIF(A1,NOW(),"ym")) Then just use: =AVERAGE(A1:A10) which is *not* array-entered. ...

Transaction Date VS Business Date
Our accounting department needs/wants to have all RMS SO reporting by business date opposed to transaction date. Usually these are the same unless you have sales and batches that close after midnight. For our store I would like to set 3 a.m. as the business day closing time. So when I run any type of reports I would like to get the information for a business date (range) starting at 3 a.m. till 3 a.m. the next day. I guess I could accomplish this by subtracting 3 hours from all the transaction (date and) times but I would have to do this for every single report which would be a lot of ...

Formula displays does not calculate
Hello: I just typed a formula in a cell and it displays =A4+J13 (the same formula calculates fine in a different cell), instead of claculating the value. Both A4 and J13 contain numeric data. I try Ctrl+~ to toggle and chk if full formula mode was ON? but that did not help either. I also tried looking for feedback through the formula evaluation utility in Excel, no help there either, it says cell being evaluated contains a constant? Your help is appreciated Hi Neil, Perhaps the cell is formatted as text? --- Regards, Norman "Neil Bhandar" <Neil Bhandar@discussions.mic...

hide system defined views in CRM 4.0
In CRM 3.0 you could share the System view to remove them from a users list of views. I cannot see the Share functionality of Views in CRM 4.0 to do the same thing. Can we please have that functionality back Thanks ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. ...

Conditional Formulas
I am working on a workbook where I would like to use a different formula depending on the result of a cell. This without using an if/then statement. That is, if the result of sheet2.C1 > 0, then the formula in A1 would be "=K5" If the result of sheet2.C1 = 0, then the formula in A1 would be "=S5" I can't use a simple if/then statement such as if('sheet2'!C1 > 0, K5, S5) because sheet2 may be deleted. Once sheet2 is deleted, the formula would cause #REF error. I thought of using Scenarios but not sur how that works. Is than any way to accomplish t...

Can I copy formulas "as they are" when they are not static
Hi, I have numerous cells with formulas in them, and I would like to transfer them to other location without formulas changing. They need to be dynamic before and after, but not during this transfer Don't ask me why ;-) -Sirritys Find and replace all "=" with "~", move and change back -- Kind regards, Niek Otten Microsoft MVP - Excel "Sirritys" <aki.koikkalainen@hotmail.com> wrote in message news:1154937837.006411.154950@m73g2000cwd.googlegroups.com... | Hi, | | I have numerous cells with formulas in them, and I would like to | transfer them to...

Tricky Formula.. Please Help
I have created a time roster.. easy in each day I have start,end and break deduction.. still no probs I need to create an output formula for hours worked after a 16:00 from the start and end range on a day. So in the roster it will still display normal hours, however I will create a field for hours after 16:00. this is for calculating a different wage rate. Thanks Aaron See response in .programming -- HTH RP (remove nothere from the email address if mailing direct) "Aaron H" <aaron@istarnetworks.com.au> wrote in message news:uU96kXI$EHA.2076@TK2MSFTNGP15.phx.gbl... ...

Post Dated Checks
I had to prepare some post dated checks and we did not have fiscal periods set up yet to match the dates of the checks. Currently the check batches are waiting to be posted. What is the best way to handle post dated checks? If I create new fiscal years to match these dates, I will end up having 4 Open years. Is this the only way to do it? You can manage post dated checks in Microsoft Dynamics GP with the Payment Document Management module available on the Feature Pack 1 DVD image. For more info, take a look at MVP Mohammad Daoud's post on the subject at: https://communit...

Remove "Date: Yesterday" or "Date: Monday" from Outlook Inbox?
Outlook mail inbox now has headers that tell you what day the the email was sent. I find this visually annoying. Each emailalready has the time and day it was sent. I don't need the daily titles in the midst of the list of emails. How do I make them go away? I think you are talking about OL 2003 & the Arrange in Groups setting. Change it in View, Arrange by, uncheck Show in Groups. "silky" wrote: > Outlook mail inbox now has headers that tell you what day the the email was > sent. I find this visually annoying. Each emailalready has the time and day >...

Help with an Excel formula
Hey, wondering if anyone can help me with a formula i need it to recognise that if the answer is negative it needs to be red. I think i need an if/then statement but do not know how?? Eg say the formula was just simply = F15-G15 then i need something to say if this anwer is negative then highlight red any help would be great thanks Create a custom format. Right click on the cell, choose Format Cells... On the Number tab, choose Custom. Try one of the formats with [Red] in it. If you like it, great. If not, you can create your own format. The rule is anythi...

strange date format problem
Hi, I downloaded some data on excel using Bloomberg. One column has dates and the other has numbers. Dates appear as 01/01/1980 format which I want in Jan-80 format. I have changed the format cell properties but it still appears as 01/01/1980. I have to goto individual cell and press F2 <enter> so see the effect. Dont know why? I cant even copy paste format after that .... TIA/amag I am not sure, but is your workbook set to Auto-Recalculate? Try pushing <F9> to do a manual re-calculation, see if that changes the formats. If it does, go to Tools (on the menu bar), Options, Ca...

How to sort by name then date?
I would like to look through all the emails to a particular person. If I click on the "To" column I can see all the emails to a particular person (actually there is a problem with that which I will mention next) but they seem to be in random order. Is there a way to have them sorted by date? (i.e. by name first, then by date). Actually, sorting on the "To" column does not get all the emails sent to a given email address. Because Outlook uses the "display as" field. It will often give different "display as" names to the same email address. C...

help with a formula please
Hello I have a text field '2007 Aug' , which I want to turn into a date 15/8/2007 so I can do some calculations with it. I've tried splitting the field etc but I'm getting into a real mess. Can someone help me with the formula pls ?? Thanks KK On dec. 9, 13:55, "KRK" <tre...@yeleek.nospam.freeserve.co.uk> wrote: > Hello > > I have a text field =A0'2007 Aug' , which I want to turn into a date 15/8= /2007 > so I can do some calculations with it. I've tried splitting the field etc > but I'm getting into a r...

Concatenate including a date so that the date appears as a date
When I try to join the contents of several cells, one of which is a date and time obtained from =now(), the date and time part appears as a number. How can I make it appear as a date and time? I have tried changing the cell formats - probably there is a combination that works but I haven't found it. I also tried copying the original date cell to another cell and changing the latter cell's format and concatenating using that latter cell, but still no success. Use the TEXT function. For example, =TEXT(NOW(),"mmmm d, yyyy") would give January 6, 2010 "dd...

How do I make a poster using 4 sheetss?
I am trying to turn a 81/2 by 11 page into 4 sheet poster? File, Page setup Use dimensions of aprox 16"x20" You may have to adjust due to the margins. -- Don Vancouver, USA "making a poster" <making a poster@discussions.microsoft.com> wrote in message news:36663F0A-DF47-48F9-959D-50885D4552F7@microsoft.com... >I am trying to turn a 81/2 by 11 page into 4 sheet poster? ...

if statement with dates #2
I would like to have a statement like: IF(a1>0,1/1/2004) with 1/1/2004 beint a date. EXCEL is interpreting 1/1/2004 as an arithmetic statement and giving me the result of the arithemtic. I could put in the numeric offste for the date and write IF(a1>0,37987), formatting the cell as a date, but this seems awkward. What else can I do? TIA, Sanford ...

Embedded pictures #4
Is there a way in Outlook (or from an Exchange 2003 Server) to have e-mails with embedded pictures NOT show the picture until you click on it, or automatically move any emails with Embedded pictures to a specific folder? Thanks, Scott There's the option in Outlook to not show pictures until you choose an option in the InfoBar. Would that be what you'd like? "Scott" <spaul@sayrevillek12.net> wrote in message news:1161091049.468637.96670@k70g2000cwa.googlegroups.com... > Is there a way in Outlook (or from an Exchange 2003 Server) to have > e-mails with embedde...

Counting Dates?
=COUNTIF(A1:A100,"<" & NOW()) This works good for specific cells. I have a similar question. How would I do the same thing given that: 1) You only have a starting date 2) Your only wanting to count work days (in this case 4 day/wk) up to NOW() Thank you for all your help. Aviator On Thu, 6 Jan 2005 12:53:03 -0800, "Aviator" <Aviator@discussions.microsoft.com> wrote: >=COUNTIF(A1:A100,"<" & NOW()) > >This works good for specific cells. I have a similar question. > >How would I do the same thing given that: >1) You only ha...

Count Unique Cells, within a date range (dates stored in separate column).
Hello All: I am attempting to create a formula that will count the number of unique cells (alpha-numeric) in an entire column (no set length, so ex: "$B:$B"). This count will be based on a date range (Between 2 dates). The dates are stored in another column (Ex: "$A:$A"). The following is an example of the cells. Dates PO_Num 1/2/2010 A123456 1/3/2010 B453453 2/2/2010 A123456 3/4/2010 C2342F3K 4/3/2010 123456 I would like to know a formula that can count the number of different PO's based on a given date range. Please help me if you...

How to redeploy CRM 4 on another server in the same domain
Hi, We are going to redeploy CRM 4 on another server in the same domain and I was wondering if there are any instructions on how to do this? Thanks Hi Sophie - are you trying to create a second deployment, or are you trying to move your existing deployment to new hardware? Dave Ireland "Sophie" <Sophie@discussions.microsoft.com> wrote in message news:D8B8E232-AC06-4EDC-8B64-B8227DEF3103@microsoft.com... > Hi, > > We are going to redeploy CRM 4 on another server in the same domain and I > was wondering if there are any instructions on how to do this? > &...

Outlook 2003 not responding #4
Within the last few days, Outlook 2003 stops responding while I am opening mail messages. Task Manager says it's not running. I have used the Repair function in Control Panel and then it will work for a little while. Starts getting slow and then stops completely. Any ideas? ...

An expression for dates from and to
I am trying to create an expression to select items from a date field falling from a certain date to a certain date. I am obviously missing something in my expression. The field name is DateComplete. How can I set up this expression to pull date from a certain date to a certain date? -- Ronnie Hi Ronnie, An example of the SQL would be: SELECT * FROM [TABLE NAME] WHERE DateComplete Between #1/1/2006# And #1/1/2007# ; On May 23, 3:52 pm, Ronnie <Ron...@discussions.microsoft.com> wrote: > I am trying to create an expression to select items from a date field falling > from a...

Linking formula #2
I have two sheets, and I want to link information from one to the other. The difficulty is that on sheet 1, the data is entered in every consecutive cell down a column, but on sheet 2, I want that data linked to every third cell down the column. So, it goes like this: A1 on sheet 1 - A1 on sheet 2 A2 on sheet 1 - A4 on sheet 2 A3 on sheet 1 - A7 on sheet 2 and so on. Can somebody help? Jonathan -- Jonibenj ------------------------------------------------------------------------ Jonibenj's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17048 View...

Returning only MAX(date) not all dates
I want to be able to list the most current date an item is checked out. As I was working through the query I used max as an aggregate function but I still can't get the simple result I want. I'd rather list the fields I have chosen and somehow select just the most current checkout date. Any help would be greatly appreciated. Here is my query: SELECT tblTerritory.TerritoryID, [TerritoryName] & " " & [TerritoryDescription] AS Description, tblTerritory.TerritoryTypeID, tblTerritoryType.TerritoryType, Max(tblTerritoryCheckout.DateCheckedOut) AS Checked...