• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If Statments for Dates and Times

J

janwork

New Member
I am trying to figure out a few calculations to perform the following funtions:

1) List the production week - Example: WK 18 falls between 11/7/11 and 11/13/11, Wk 19 falls between dates 11/14/11.

I want to be able to enter a formula that can handle up to 10 WK#'s, checking a field with a single date and give me the correct WK it fall under.


2) Calculate the # of days from a Processed date to a conditional date - Example: If the Form Received date is populated, calculate the # of days based on the Processed date to the Form Received date. if the Form Received Date is blank, calculate the number of days based on the Processed date to the current date.
 
1. use weeknum() if you have MS 2007 or after. not sure if 2003 has this function or not.


2. can you show some data sample? I don't quite understand what you need.
 
Hi ,


1. If you say week 18 falls between 11/7/2011 and 11/13/2011 , then we can calculate when this calendar has started i.e. when does week 1 start ? Of course , if you already have a complete calendar , then enter the first day of this calendar ( the start of week 1 ) in any cell , say A1.


Now , given any date D , to calculate which week it falls in , use the following formula :


=INT((D-$A$1+1)/7)+1


Replace D by the cell address of the date whose week you wish to calculate.


2. A combination of the IF function and the ISBLANK function will do the job :


=IF(ISBLANK(Form_Received_Date),TODAY()-Processed_Date,Processed_Date-Form_Received_Date))


Narayan
 
You must log in or register to reply here.
Back
Top