Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Evaluating Week No.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Evaluating Week No.
Hi I am working on this PAYSLIP sheet. I want to add this feature in payslip
to find out the Week NO. on the basis of w/e dates entered which could be
different from the standard table (below).
i.e If date enterd is 07/05/2004 then its lessthan 10/05/2004 and
greaterthan 03/05/2004 than week no=5
It would have been easy if the options were just 2, but how can i evaluate
it from the table(below)?
I do not know how to start! any ideas please




W/E Week No
12/04/2004 1
19/04/2004 2
26/04/2004 3
03/05/2004 4
10/05/2004 5
17/05/2004 6
24/05/2004 7
31/05/2004 8
07/06/2004 9
14/06/2004 10
21/06/2004 11
28/06/2004 12
05/07/2004 13
12/07/2004 14
19/07/2004 15
26/07/2004 16
02/08/2004 17
09/08/2004 18
16/08/2004 19
23/08/2004 20
30/08/2004 21
06/09/2004 22
13/09/2004 23
20/09/2004 24
27/09/2004 25
04/10/2004 26
11/10/2004 27
18/10/2004 28
25/10/2004 29
01/11/2004 30
08/11/2004 31
15/11/2004 32
22/11/2004 33
29/11/2004 34
06/12/2004 35
13/12/2004 36
20/12/2004 37
27/12/2004 38
03/01/2005 39
10/01/2005 40
17/01/2005 41
24/01/2005 42
31/01/2005 43
07/02/2005 44
14/02/2005 45
21/02/2005 46
28/02/2005 47
07/03/2005 48
14/03/2005 49
21/03/2005 50
28/03/2005 51
04/04/2005 52


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 102
Default Evaluating Week No.
MahaRaj wrote:

Hi I am working on this PAYSLIP sheet. I want to add this feature in
payslip to find out the Week NO. on the basis of w/e dates entered
which could be different from the standard table (below).
i.e If date enterd is 07/05/2004 then its lessthan 10/05/2004 and
greaterthan 03/05/2004 than week no=5
It would have been easy if the options were just 2, but how can i
evaluate it from the table(below)?
I do not know how to start! any ideas please




W/E Week No
12/04/2004 1
19/04/2004 2
26/04/2004 3
03/05/2004 4
10/05/2004 5
17/05/2004 6
24/05/2004 7
31/05/2004 8
07/06/2004 9
14/06/2004 10
21/06/2004 11
28/06/2004 12
05/07/2004 13
12/07/2004 14
19/07/2004 15
26/07/2004 16
02/08/2004 17
09/08/2004 18
16/08/2004 19
23/08/2004 20
30/08/2004 21
06/09/2004 22
13/09/2004 23
20/09/2004 24
27/09/2004 25
04/10/2004 26
11/10/2004 27
18/10/2004 28
25/10/2004 29
01/11/2004 30
08/11/2004 31
15/11/2004 32
22/11/2004 33
29/11/2004 34
06/12/2004 35
13/12/2004 36
20/12/2004 37
27/12/2004 38
03/01/2005 39
10/01/2005 40
17/01/2005 41
24/01/2005 42
31/01/2005 43
07/02/2005 44
14/02/2005 45
21/02/2005 46
28/02/2005 47
07/03/2005 48
14/03/2005 49
21/03/2005 50
28/03/2005 51
04/04/2005 52

You could start with the ATP Weeknum function, or read this:
http://www.cpearson.com/excel/weeknum.htm
I see your year starts on my week 16. So you'd have to subtract 15 from
the weeknumber. I hope you can work it out yourself how to make the
"negative" weeknumbers work in your system.

--
Amedee Van Gasse

To top-post is human, to bottom-post and snip is sublime.

Dit bericht is geplaatst in een nieuwsgroep. Post je evt antwoord of
vervolgvraag graag in dezelfde thread in de nieuwsgroep a.u.b.
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Evaluating Week No.
Start out with

=VLOOKUP(E6,B8:C59,2,1)

Assumes E6 has the date of interest and your table is in B3:C59

--
Regards,
Tom Ogilvy

"MahaRaj" wrote in message
...
Hi I am working on this PAYSLIP sheet. I want to add this feature in
payslip
to find out the Week NO. on the basis of w/e dates entered which could be
different from the standard table (below).
i.e If date enterd is 07/05/2004 then its lessthan 10/05/2004 and
greaterthan 03/05/2004 than week no=5
It would have been easy if the options were just 2, but how can i evaluate
it from the table(below)?
I do not know how to start! any ideas please




W/E Week No
12/04/2004 1
19/04/2004 2
26/04/2004 3
03/05/2004 4
10/05/2004 5
17/05/2004 6
24/05/2004 7
31/05/2004 8
07/06/2004 9
14/06/2004 10
21/06/2004 11
28/06/2004 12
05/07/2004 13
12/07/2004 14
19/07/2004 15
26/07/2004 16
02/08/2004 17
09/08/2004 18
16/08/2004 19
23/08/2004 20
30/08/2004 21
06/09/2004 22
13/09/2004 23
20/09/2004 24
27/09/2004 25
04/10/2004 26
11/10/2004 27
18/10/2004 28
25/10/2004 29
01/11/2004 30
08/11/2004 31
15/11/2004 32
22/11/2004 33
29/11/2004 34
06/12/2004 35
13/12/2004 36
20/12/2004 37
27/12/2004 38
03/01/2005 39
10/01/2005 40
17/01/2005 41
24/01/2005 42
31/01/2005 43
07/02/2005 44
14/02/2005 45
21/02/2005 46
28/02/2005 47
07/03/2005 48
14/03/2005 49
21/03/2005 50
28/03/2005 51
04/04/2005 52




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Evaluating Week No.
Check your system clock. You are posting in the future.

--
Regards,
Tom Ogilvy

"MahaRaj" wrote in message
...
Hi I am working on this PAYSLIP sheet. I want to add this feature in
payslip
to find out the Week NO. on the basis of w/e dates entered which could be
different from the standard table (below).
i.e If date enterd is 07/05/2004 then its lessthan 10/05/2004 and
greaterthan 03/05/2004 than week no=5
It would have been easy if the options were just 2, but how can i evaluate
it from the table(below)?
I do not know how to start! any ideas please




W/E Week No
12/04/2004 1
19/04/2004 2
26/04/2004 3
03/05/2004 4
10/05/2004 5
17/05/2004 6
24/05/2004 7
31/05/2004 8
07/06/2004 9
14/06/2004 10
21/06/2004 11
28/06/2004 12
05/07/2004 13
12/07/2004 14
19/07/2004 15
26/07/2004 16
02/08/2004 17
09/08/2004 18
16/08/2004 19
23/08/2004 20
30/08/2004 21
06/09/2004 22
13/09/2004 23
20/09/2004 24
27/09/2004 25
04/10/2004 26
11/10/2004 27
18/10/2004 28
25/10/2004 29
01/11/2004 30
08/11/2004 31
15/11/2004 32
22/11/2004 33
29/11/2004 34
06/12/2004 35
13/12/2004 36
20/12/2004 37
27/12/2004 38
03/01/2005 39
10/01/2005 40
17/01/2005 41
24/01/2005 42
31/01/2005 43
07/02/2005 44
14/02/2005 45
21/02/2005 46
28/02/2005 47
07/03/2005 48
14/03/2005 49
21/03/2005 50
28/03/2005 51
04/04/2005 52




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Evaluating Week No.
sorry I forgot to mention.
there a problem with my ISP NTL uk
I have the right time on my system but the the date stamps are from NTL
server so I can not do nothing. I even changed my system time one hour later
but still nothing happend.

MahaRaj

PS: I will try not to post to the ngs. :)

"Tom Ogilvy" wrote in message
...
Check your system clock. You are posting in the future.

--
Regards,
Tom Ogilvy

"MahaRaj" wrote in message
...
Hi I am working on this PAYSLIP sheet. I want to add this feature in
payslip
to find out the Week NO. on the basis of w/e dates entered which could
be
different from the standard table (below).
i.e If date enterd is 07/05/2004 then its lessthan 10/05/2004 and
greaterthan 03/05/2004 than week no=5
It would have been easy if the options were just 2, but how can i
evaluate
it from the table(below)?
I do not know how to start! any ideas please




W/E Week No
12/04/2004 1
19/04/2004 2
26/04/2004 3
03/05/2004 4
10/05/2004 5
17/05/2004 6
24/05/2004 7
31/05/2004 8
07/06/2004 9
14/06/2004 10
21/06/2004 11
28/06/2004 12
05/07/2004 13
12/07/2004 14
19/07/2004 15
26/07/2004 16
02/08/2004 17
09/08/2004 18
16/08/2004 19
23/08/2004 20
30/08/2004 21
06/09/2004 22
13/09/2004 23
20/09/2004 24
27/09/2004 25
04/10/2004 26
11/10/2004 27
18/10/2004 28
25/10/2004 29
01/11/2004 30
08/11/2004 31
15/11/2004 32
22/11/2004 33
29/11/2004 34
06/12/2004 35
13/12/2004 36
20/12/2004 37
27/12/2004 38
03/01/2005 39
10/01/2005 40
17/01/2005 41
24/01/2005 42
31/01/2005 43
07/02/2005 44
14/02/2005 45
21/02/2005 46
28/02/2005 47
07/03/2005 48
14/03/2005 49
21/03/2005 50
28/03/2005 51
04/04/2005 52






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Evaluating Week No.
I wouldn't suggest anything as drastic as not posting to the newsgroups. If
there is nothing you can do about it, then that is the way it is. It just
causes your messages to line up out of sequence (for me at least) - but so
be it.

--
Regards,
Tom Ogilvy

"MahaRaj" wrote in message
...
sorry I forgot to mention.
there a problem with my ISP NTL uk
I have the right time on my system but the the date stamps are from NTL
server so I can not do nothing. I even changed my system time one hour
later
but still nothing happend.

MahaRaj

PS: I will try not to post to the ngs. :)

"Tom Ogilvy" wrote in message
...
Check your system clock. You are posting in the future.

--
Regards,
Tom Ogilvy

"MahaRaj" wrote in message
...
Hi I am working on this PAYSLIP sheet. I want to add this feature in
payslip
to find out the Week NO. on the basis of w/e dates entered which could
be
different from the standard table (below).
i.e If date enterd is 07/05/2004 then its lessthan 10/05/2004 and
greaterthan 03/05/2004 than week no=5
It would have been easy if the options were just 2, but how can i
evaluate
it from the table(below)?
I do not know how to start! any ideas please




W/E Week No
12/04/2004 1
19/04/2004 2
26/04/2004 3
03/05/2004 4
10/05/2004 5
17/05/2004 6
24/05/2004 7
31/05/2004 8
07/06/2004 9
14/06/2004 10
21/06/2004 11
28/06/2004 12
05/07/2004 13
12/07/2004 14
19/07/2004 15
26/07/2004 16
02/08/2004 17
09/08/2004 18
16/08/2004 19
23/08/2004 20
30/08/2004 21
06/09/2004 22
13/09/2004 23
20/09/2004 24
27/09/2004 25
04/10/2004 26
11/10/2004 27
18/10/2004 28
25/10/2004 29
01/11/2004 30
08/11/2004 31
15/11/2004 32
22/11/2004 33
29/11/2004 34
06/12/2004 35
13/12/2004 36
20/12/2004 37
27/12/2004 38
03/01/2005 39
10/01/2005 40
17/01/2005 41
24/01/2005 42
31/01/2005 43
07/02/2005 44
14/02/2005 45
21/02/2005 46
28/02/2005 47
07/03/2005 48
14/03/2005 49
21/03/2005 50
28/03/2005 51
04/04/2005 52








Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Evaluating Week No.
thanks for understanding.
:)
MahaRaj

"Tom Ogilvy" wrote in message
...
I wouldn't suggest anything as drastic as not posting to the newsgroups.
If
there is nothing you can do about it, then that is the way it is. It just
causes your messages to line up out of sequence (for me at least) - but so
be it.

--
Regards,
Tom Ogilvy

"MahaRaj" wrote in message
...
sorry I forgot to mention.
there a problem with my ISP NTL uk
I have the right time on my system but the the date stamps are from NTL
server so I can not do nothing. I even changed my system time one hour
later
but still nothing happend.

MahaRaj

PS: I will try not to post to the ngs. :)

"Tom Ogilvy" wrote in message
...
Check your system clock. You are posting in the future.

--
Regards,
Tom Ogilvy

"MahaRaj" wrote in message
...
Hi I am working on this PAYSLIP sheet. I want to add this feature in
payslip
to find out the Week NO. on the basis of w/e dates entered which
could
be
different from the standard table (below).
i.e If date enterd is 07/05/2004 then its lessthan 10/05/2004 and
greaterthan 03/05/2004 than week no=5
It would have been easy if the options were just 2, but how can i
evaluate
it from the table(below)?
I do not know how to start! any ideas please




W/E Week No
12/04/2004 1
19/04/2004 2
26/04/2004 3
03/05/2004 4
10/05/2004 5
17/05/2004 6
24/05/2004 7
31/05/2004 8
07/06/2004 9
14/06/2004 10
21/06/2004 11
28/06/2004 12
05/07/2004 13
12/07/2004 14
19/07/2004 15
26/07/2004 16
02/08/2004 17
09/08/2004 18
16/08/2004 19
23/08/2004 20
30/08/2004 21
06/09/2004 22
13/09/2004 23
20/09/2004 24
27/09/2004 25
04/10/2004 26
11/10/2004 27
18/10/2004 28
25/10/2004 29
01/11/2004 30
08/11/2004 31
15/11/2004 32
22/11/2004 33
29/11/2004 34
06/12/2004 35
13/12/2004 36
20/12/2004 37
27/12/2004 38
03/01/2005 39
10/01/2005 40
17/01/2005 41
24/01/2005 42
31/01/2005 43
07/02/2005 44
14/02/2005 45
21/02/2005 46
28/02/2005 47
07/03/2005 48
14/03/2005 49
21/03/2005 50
28/03/2005 51
04/04/2005 52










Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,824
Default Evaluating Week No.
You may want to verify that your time zone is correct, too.

Windows start button|settings|control panel|Date/Time Applet|Time Zone tab
(is how I'd get to in win98)





MahaRaj wrote:

thanks for understanding.
:)
MahaRaj

"Tom Ogilvy" wrote in message
...
I wouldn't suggest anything as drastic as not posting to the newsgroups.
If
there is nothing you can do about it, then that is the way it is. It just
causes your messages to line up out of sequence (for me at least) - but so
be it.

--
Regards,
Tom Ogilvy

"MahaRaj" wrote in message
...
sorry I forgot to mention.
there a problem with my ISP NTL uk
I have the right time on my system but the the date stamps are from NTL
server so I can not do nothing. I even changed my system time one hour
later
but still nothing happend.

MahaRaj

PS: I will try not to post to the ngs. :)

"Tom Ogilvy" wrote in message
...
Check your system clock. You are posting in the future.

--
Regards,
Tom Ogilvy

"MahaRaj" wrote in message
...
Hi I am working on this PAYSLIP sheet. I want to add this feature in
payslip
to find out the Week NO. on the basis of w/e dates entered which
could
be
different from the standard table (below).
i.e If date enterd is 07/05/2004 then its lessthan 10/05/2004 and
greaterthan 03/05/2004 than week no=5
It would have been easy if the options were just 2, but how can i
evaluate
it from the table(below)?
I do not know how to start! any ideas please




W/E Week No
12/04/2004 1
19/04/2004 2
26/04/2004 3
03/05/2004 4
10/05/2004 5
17/05/2004 6
24/05/2004 7
31/05/2004 8
07/06/2004 9
14/06/2004 10
21/06/2004 11
28/06/2004 12
05/07/2004 13
12/07/2004 14
19/07/2004 15
26/07/2004 16
02/08/2004 17
09/08/2004 18
16/08/2004 19
23/08/2004 20
30/08/2004 21
06/09/2004 22
13/09/2004 23
20/09/2004 24
27/09/2004 25
04/10/2004 26
11/10/2004 27
18/10/2004 28
25/10/2004 29
01/11/2004 30
08/11/2004 31
15/11/2004 32
22/11/2004 33
29/11/2004 34
06/12/2004 35
13/12/2004 36
20/12/2004 37
27/12/2004 38
03/01/2005 39
10/01/2005 40
17/01/2005 41
24/01/2005 42
31/01/2005 43
07/02/2005 44
14/02/2005 45
21/02/2005 46
28/02/2005 47
07/03/2005 48
14/03/2005 49
21/03/2005 50
28/03/2005 51
04/04/2005 52









--

Dave Peterson

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert regular Date to Week Ending or Week Beginning Dates Sam H Excel Discussion (Misc queries) 5 April 3rd 23 04:39 PM
week end and week so far results formular in % format happyhammer Excel Discussion (Misc queries) 3 August 11th 09 09:01 PM
Auto calculate day of week to week of the year (not as serial) oftenconfused Excel Discussion (Misc queries) 4 June 23rd 08 05:14 PM
How do I set up a week by week skill training schedule in excel? davidwatts Excel Discussion (Misc queries) 0 June 16th 05 11:32 PM
I need week number in excell from a date, first week must be mini. jPeich Excel Discussion (Misc queries) 4 January 5th 05 01:21 AM


All times are GMT +1. The time now is 06:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright �2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement