Converting Date to Work Week...

Hello,

I'm trying to convert a colum of entry dates to work weeks.  I have a
helper row for month and year but I do not know if there is a quick way
(or a built in formula in excel) to determine the "work week" (within a
month).

For example:

ENTRY DATE:      Month:     Work Week:
8/26/05             August       4
8/19/05             August       3

etc.

Is there a formula that I can drag down this list of about 1,800 rows
with all different months?

Thanks,

PZan


-- 
PokerZan
------------------------------------------------------------------------
PokerZan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23480
View this thread: http://www.excelforum.com/showthread.php?threadid=400134

0
8/29/2005 4:47:19 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
580 Views

Similar Articles

[PageSpeed] 45

On Mon, 29 Aug 2005 11:47:19 -0500, PokerZan
<PokerZan.1ujcib_1125335108.8204@excelforum-nospam.com> wrote:

>
>Hello,
>
>I'm trying to convert a colum of entry dates to work weeks.  I have a
>helper row for month and year but I do not know if there is a quick way
>(or a built in formula in excel) to determine the "work week" (within a
>month).
>
>For example:
>
>ENTRY DATE:      Month:     Work Week:
>8/26/05             August       4
>8/19/05             August       3
>
>etc.
>
>Is there a formula that I can drag down this list of about 1,800 rows
>with all different months?
>
>Thanks,
>
>PZan

It's probably possible, but you will have to define 

	" 'work week' (within a month)"

In other words, what is your definition of the first day of the first week?
What day does the week change?

August is too easy, since 1 Aug 2005 was a Monday.

What week is 1 Sep 2005?
What week is 5 Sep 2005?



--ron
0
ronrosenfeld (3122)
8/29/2005 5:47:59 PM
There is no built in formula for work week.

"PokerZan" wrote:

> 
> Hello,
> 
> I'm trying to convert a colum of entry dates to work weeks.  I have a
> helper row for month and year but I do not know if there is a quick way
> (or a built in formula in excel) to determine the "work week" (within a
> month).
> 
> For example:
> 
> ENTRY DATE:      Month:     Work Week:
> 8/26/05             August       4
> 8/19/05             August       3
> 
> etc.
> 
> Is there a formula that I can drag down this list of about 1,800 rows
> with all different months?
> 
> Thanks,
> 
> PZan
> 
> 
> -- 
> PokerZan
> ------------------------------------------------------------------------
> PokerZan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23480
> View this thread: http://www.excelforum.com/showthread.php?threadid=400134
> 
> 
0
Mike1154 (1216)
8/29/2005 8:56:44 PM
Well thanks guys, I was able to dig up a work around.

I am using WEEKNUM to calculate annual weeks, then use the common week
to gather my data on a weekly basis.  When I thought about it a bit mor
having it broken down into monthly work weeks wasn't exactly necessary
the entry date just had to be in the same week (common).

WEEKNUM is a great little tool that I had not used up to this point
but RTFM'd week number in help and it worked like a charm.

All the best!

PZa

--
PokerZa
-----------------------------------------------------------------------
PokerZan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2348
View this thread: http://www.excelforum.com/showthread.php?threadid=40013

0
8/30/2005 2:46:28 PM
On Tue, 30 Aug 2005 09:46:28 -0500, PokerZan
<PokerZan.1ul1md_1125414312.5445@excelforum-nospam.com> wrote:

>
>Well thanks guys, I was able to dig up a work around.
>
>I am using WEEKNUM to calculate annual weeks, then use the common weeks
>to gather my data on a weekly basis.  When I thought about it a bit more
>having it broken down into monthly work weeks wasn't exactly necessary,
>the entry date just had to be in the same week (common).
>
>WEEKNUM is a great little tool that I had not used up to this point,
>but RTFM'd week number in help and it worked like a charm.
>
>All the best!
>
>PZan

The only problem with WEEKNUM, which may or may not be a problem for you but
you should be aware of, is that there is a standard ISO weeknumber; and WEEKNUM
does not conform to this specification.

If this is an issue, post back.


--ron
0
ronrosenfeld (3122)
9/1/2005 8:47:59 PM
Reply:

Similar Artilces:

Converting a Microsoft Word document to a PC document
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have created a Resume and a cover letter in Microsoft Word on a Mac to be uploaded to the Institute ORACLE recruitment system. <br><br>When I checked how it looked, the format, the bullets and formating were blown apart! I was told to convert the documents to PC format. <br><br>I don't know how to do this. I got some guidance to make them PDF files, but the formating still goes haywire! How do I do the conversion on my Mac? Hi Meg: The most important thing to do is DO NOT TEL...

Money Changes Transaction Dates
Hi all, How can I stop Money2004 from changing my transaction dates that I have already entered into the register? This happens when I accept/match downloaded transactions from my bank. Thanks! Well, I think I just answered my own question. I found a check box to untick under online options to fix this, but I will post back if necessary. "rustyfender04" <rustyfender1@hotmail.com> wrote in message news:eY3zaLITHHA.2124@TK2MSFTNGP06.phx.gbl... > Hi all, > > How can I stop Money2004 from changing my transaction dates that I have > already entered into t...

adding months to an inputted date
I need a function that will take a date that a user has typed in a different cell and will then add two months to the date. For instance, if I type "2/12/05" in B1, then I want C2 to be: "4/12/05". Thank you for any help that you may be able to give. Logan =DATE(YEAR(B1),MONTH(B1)+2,DAY(B1)) however what do you want the date to be in C2 if B1 is 01/30/05? Regards, Peo Sjoblom "BLW" wrote: > I need a function that will take a date that a user has typed in a different > cell and will then add two months to the date. For instance, if I type >...

Rules not working #7
I have a mail profile that I use with one email address that gets a LOT of spam. I have a fair size number of rules (probably 10-15) for categorizing and filtering it, all of them starting wth "Apply this rule after the message arrives". Problem is that most of them only work about half the time. I'll get some messages that were moved to the correct folders, and some similar messages still in the inbox. If I click "Rules Wizard" and the "Run Now" button, selecting all the rules, it moves the ones still in the inbox to the correct folder. How come it didn&#...

Convert date to months
I have a database that has multiple dates in it. What I am trying to do is in one field I have a date and I am want that date to convert to months in another field. Can anyone help me on this. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1 are you trying to find months between dates? or convert 4 to April? Look at format function for ways to display months, datediff function for months difference.. "bohon79 via AccessMonster.com" <u35329@uwe> wrote in message news:74ac2f3f40ce9@uwe... >I have a database ...

can i send a weekly calender to others?
I want to send a weekly calender to my collegues. How do i do this?? What version of Outlook? Do you want to send them a calendar they can open in Outlook or will a calendar in a word document do? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Poll: What version of Exchange server do y...

Out of Office (OOF) not working
Hello OOF isnt working on one of our users mailbox, ie. she turns it on, but it doesnt auto reply. ive searched through the existing posts on solutions but still no joy. Ive disabled/re-enable OOF setting in Exchange admin. The user has no exisitng rules setup. The oof never worked so it cannot have been send one per user. Im using Exchange 2003 with Outlook 2003 clients. Any help, would help! thanks Jay If new mailboxes are working fine, you may want to take the simple "shotgun" approach of exporting the users data to PST, saving out the rules, and then creating a new mailbo...

hyperlinks within the same document are not working
Hi. I am trying to create an email publication in MS publisher 2007. It has several stories that will be sent out in email and the first section is supposed to contain a summary with hyperlinks to each story within the same email for more details. I am adding a bookmark at the begining of each story, and then trying to create a hyperlink in the summary section to the bookmark. However, when I send the publication as email, or do an email preview, the hyperlinks do not work. Please help! ...

Hyperlinks do not work in email in outlook
Shortly after installing the new service pack for Windows XP I began getting this message whenever I tried to click on a hyperlink in an email: "This operation has been canceled due to restrictions in effect on this computer. Please contact your system administrator." I assume it is because some switch has been set. Could someone tell me how to regain the ability to use the embedded links in email in outlook? Thanks Bob <anonymous@discussions.microsoft.com> wrote: > Shortly after installing the new service pack for Windows > XP I began getting this message whene...

Outlook 2003 working with Groupwise client
We are moving our people from Groupwise to Exchange and I am having a horrible time getting the two to work together. We need to keep both clients on the machine for a short time while the switch is done. I installed Outlook 2003 on my workstation running Windows XP and Outlook and Groupwise are happily coexisting. When I tried this on a Windows 2000 machine everything went to crap. I've tried all of the tricks I can think of. I've removed Groupwise and windows messaging and reintalled everything from scratch and no dice. I just keep getting Outlook MAPI errors and messages s...

Exchange 2003 Envelope Journaling not working
Hi, I have a single Exchange 2003 SP1 server. I'm trying to get envelope journaling to work. I have enabled message journaling and then used "exejcfg.exe -e" to enable envelope journaling. The journal mailbox is receiving the standard message journal messages, but not the envelope journal formatted messages. I have verified with LDP to confirm the heuristic value is 512 for the Exchange Org object. Anyone else seen this before or have any advice? Thanks --- Greg Reboot maybe? greg@intellireach.com wrote: > Hi, > > I have a single Exchange 2003 SP1 server. I...

Need to convert point on screen to various screen resolutions
Let's say you click on a button on your screen at 1000,2500 TWIPS and your resolution is 800 X 600. Now you change your screen resolution to 1024 X 768 and you need to click on the same button in it's new location on the screen. Who's 100 times smarter than I am and can do some tricky math that will tell me the TWIPS to find that button? I'd ned to do the same calculation for other screen resolutions like 640 X 480 etc. http://www.applecore99.com/api/api012.asp -- Regards, Tom Ogilvy "Donna YaWanna" <diy@mdahospital.com> wrote in message news:%23HMLEWW2...

DB Mail is is not working. Need help please.
I have try stop and start the db mail by executing the SPs below but no luck. Does anyone has seem the error below and how to fix it. Thanks in advance for your advise/help. --steps USE msdb; go EXECute sysmail_stop_sp; go EXECute sysmail_start_sp; go EXECute sysmail_help_queue_sp @queue_type = 'Mail'; go queue_type length state last_empty_rowset_time last_activated_time ---------- ----------- ---------------------------------------------------------------- ----------------------- ------------...

How do I get automatic completion to work for an in-cell dropdown
If I have a list of Alpha, Beta, Gamma and an in-cell dropdown, I'd like typing 'G' to auto-select Gamma. I'm pretty sure it was doing that at some point, but now, it does not. This isn't supported under data|validation. You could use A,B,G in the dropdown and then use a helper cell with a formula in it, though. Say you put your abbreviations in A1:A3 of sheet2 and the real names in B1:B3, then you could use: =if(a1="","",vlookup(a1,sheet2!a:b,2,false)) Joseph Weisblatt wrote: > > If I have a list of Alpha, Beta, Gamma and an in-cell dropd...

failure of Open XML 1.1.4 to work
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Previous versions of this worked fine. This version comes up with comment &quot;not enough memory&quot;. As I have 2mg Ram this seems strange. Any help appreciated. I'm afraid you haven't provided sufficient information for anyone to go on, especially since that message quite often has nothing to do with memory. For any questions of a technical or performance nature it's important that you specify your current update level of OS X as well as for Office. Exactly when does this messag...

How do I convert a list to an Excel file?
I have a WORD file with 48 lines of comma delimited data in the form: xxx,xxxxxxx,xxxxxx,x,x,xxx,xxxxxxxxx xx,xxx,xxx,xxx,xxx,xx,x I would like to convert the WORD list to EXCEL. When I attempt to open the WORD file in EXCEL, I thought a conversion window would appear....what I actually get is "incorrect format" Hi! Save the WORD file as a plain text file. Then, when you open the file in Excel a Wizard will open and step you through it. Just select COMMA as the delimiter. Biff >-----Original Message----- >I have a WORD file with 48 lines of comma delimited data in ...

Calendar/Dates Help
Hi All, I have an excel spreadsheet that lists every date in the year, with a particular code in the next cell. IE: Monday 3/01/2005 11M 22M 32M Tuesday 4/01/2005 11T 22T 32T Wednes 5/01/2005 11W 22W 32W Thursday 6/01/2005 11H 22H 32H Friday 7/01/2005 11F 22F 32F Saturday 8/01/2005 11S 22S 32S Sunday 9/01/2005 11N 22N 32N Monday 10/01/2005 11M 21M 33M Tuesday 11/01/2005 11T 21T 33T Wednes 12/01/2005 11W 21W 33W Thursday 13/01/2005 11H 21H 33H Friday 14/01/2005 11F 21F 33F What I need is to be able to search by the code eg "33T" and have all the dates listed for ...

Date comparison better method
Select col1, col2 from TableName where DateColumn BETWEEN '2010-06-01' and '2010-06-17 23:59:59.997' Select col1, col2 from TableName where WHERE DateColumn >= '2010-06-01' AND DateColumn < '2010-06-17 23:59:59.997' I am seeing in a project both the above methods of data range filering is happening in different SPs. I am trying to understand which is the better method of comparing two date values and why? [Btw i know BETWEEN considers both the upper and lower limit] Regards Pradeep I would say the following is the better approach: ...

Extracting Time from a cell that has both the date and the time
Hi Folks, I could do with some help here please. I am trying to extract the time only from a cell that has both the date and the time. Can anyone suggest a solution? Thanks in advance. :confused: -- Hani Muhtadi ------------------------------------------------------------------------ Hani Muhtadi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26794 View this thread: http://www.excelforum.com/showthread.php?threadid=466177 If you just wish the time to display, set the cell format to Time. If you wish to use the time portion then =A1-Int(A1) will give you...

_CRTDBG_MAP_ALLOC doesn't seem to work
I'm having problems tracking down a meory leak and thought the best way would be to follow the instructions in MDSN: "Detecting and Isolating Memory Leaks Using Microsoft Visual C++" However, I can only get the basic memory dump, without source file and line number. It's as if _CRTDBG_MAP_ALLOC is being ignored. I tried building a test application (basic MFC SDI application) to try it out. At the top of Leaktest.h, I have #define _CRTDBG_MAP_ALLOC #include <stdlib.h> #include <crtdbg.h> In CLeakTestApp::InitInstance(), I have _CrtSetDbgFlag( _CRTD...

OWA timeout does not work for every client
I set the timeout setting on OWA, i don't have any issues but i'm getting lots of complaints that users are getting logged of after 15 min. why does this happen with some clients but not all? thx ...

I cannot paste from one workbook to another. Copy works, paste do.
When I attempt to copy from one workbook and paste into another, copy appears to work but paste does not. The paste menu item is grayed out in the workbook to which I attempt to paste. Are there setting that can be changed to aloow the paste?? Check to see if either workbook and/or worksheet is protected. If so, unprotect. Then Copy/Paste should work. BTW, how are you copy?paste(ing) Tab or content? Dennis "JimmyMc" wrote: > When I attempt to copy from one workbook and paste into another, copy appears > to work but paste does not. The paste menu item is grayed out...

Get Start date of Week number and Year
I’d like to build the following expression in my query GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate])) So if EnteredDate = 11/3/2009 the function would return 11/1/2009 But GetStartWeekNumber does not exist as an Access Built-In Function. Is there another way to do this as an expression in a query? I’m not familiar with creating my own functions. Thanks. That would depend on how you define the start of the week... One option would be to get the day-of-week number of the date (in my system/setup, Monday is day 2), then subtract one less than that...

Journal stopped working #2
I have noticed that after upgrading from Outlook XP (2002) to Office Outlook 2003 my journal has completely stopped logging entries. I have turned it off and re-initialized it and still not entries. I mailing want it to log Word and Excel files. Any help would be appreciated I am running Exchange 2003 in cached outlook mode on a SBS 2003 server. ...

Work Schedule in Excel doc ... Don't have Excel
I just started a new job and the boss sent next weeks schedule in Exce format but I don't have excel. Is there anyway to just read an Exce file? If not can someone tell me my schedule for me -- charlene202 ----------------------------------------------------------------------- charlene2021's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1641 View this thread: http://www.excelforum.com/showthread.php?threadid=27780 Hi you can get a free viewer for excel from the microsoft site .. the following link should get you there http://office.microsoft.com/en-us/of...