Workweek start every thursday 19:00 to next thursday 19:00. How to Calculate workweek

[oligo]

Hi all,
I need help on this. Say if i have this date 1/19/13 and time 20:00,

How do i create formula to find out the workweek my data fall in, if my WW1 range is from 1/3/13 19:00 to 1/10/13 19:00, WW2 range is from 1/10/13 19:01 to 1/17/13 19:00 and so on?
WW Date Time
1 1/3/2013 19:00
2 1/10/2013 19:00
3 1/17/2013 19:00
4 1/24/2013 19:00
5 1/31/2013 19:00
6 2/7/2013 19:00

Using simple Weeknum formula cant do it.
I need to be able to lookup at my table and find out the particular workweek for 1year data. Appreciate someone can help

[benishiryo]

hi oligo, i think you meant 1/3/13 19:00 to 1/10/13 18:59? because the 2nd day would start from 19:00 again? if so, try this formula:
=CEILING((A1-DATEVALUE("2jan2013")-TIME(19,,))/7,1)

what i'm doing is making 3rd Jan 2013 become a value of 1. i then divide by 7 days a week & use CEILING to round it up by a significance of 1. (so day 1 to 7 divided by 7 would be rounded up to Week 1).

[oligo]

HI, thx for your reply.

How to take care of the time? IF my data is 1/10/13 and 10:00, the workweek suppose to be 1.
If time >19:00 then it will be workweek 2

By the way, the date and time is separate cell data i key in. i.e A1=Date, B1=Time.
If my A1 and B1 fall in thursday 19:00 to next thursday 18:59, it will give me that particular workweek.

[benishiryo]

maybe:
=CEILING(INT((A1-TIME(19,,)-DATEVALUE("2jan2013")))/7,1)

i am not sure how your file looks like, but from guessing, it might be:
=CEILING(INT((A1+B1-TIME(19,,)-DATEVALUE("2jan2013")))/7,1)

if you still cant get it, uploading a sample Excel file will enable us to help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon
ideally, it should contain your desired results

[oligo]

Hi, thanks for your reply again. Attach my excel example.

I need to return the workweek for given sched date and sched time.
If the sched date and sched time fall between the range in sheet 2, it should return the WW as results

[bebo021999]

Try this in D2:

Please Login or Register  to view this content.