if date function

[ionarodricks]

Please help

trying to get an if formula to work based on work schedule dates.
If cell a1 is a certain date eg. 20-dec-06 and is between start date and end date then what is the pay#?


Cell A1 = Date (this will change)
B1 = pay # (26 pay dates)
C1= begin date
D1= end date

example
A2 = 20-dec-06
B2= 1
C2=10-Dec-06
D2= 23-dec-06


Thank you,

Iona

[daddylonglegs]

Hello Iona,

I'm not absolutely sure what you're asking but if A1 is a date and you want to know which pay period it falls into, based on period 1 starting on 10-Dec-06 and each pay period having 14 days try

=INT((A1-DATE(2006,12,10))/14)+1

[Ron Coderre]

You'll need to post more information.

What determines the date of the first Sunday of the fiscal year?
or....
Just tell us the date of the 1st Sunday of the fiscal year, and we'll see what we can do.

[ionarodricks]

My apologies I've not been too clear. Have attached a word document with the table.

The payroll periods have already been determined from 1 to 26 each with 14 days each.

The question is given a certain date i.e. 4 july or 20th july or 23 dec (orange column) how do I determine which payroll number (1-26) do these dates fall into?

I was attempting to use the if formula however not sure its right.

Thank you very very much for replying. Really appreciate it.

Iona

[Ron Coderre]

OK....That helps immensely.

With
Your posted table on Sheet2,
beginning in cell A1,
with Row_1 containing column headings

Then....On Sheet1
A1: (a date to test)
C1: (the start of a date range)
D1: (the end of the date range)

If the test date is withing the date range,
this formula returns the pay period of the test date:

Please Login or Register  to view this content.

Note: That is assuming the date range is within the payroll year detailed on Sheet2. If that may not be the case, we can put additional tests in the formula.

Does that help?

[daddylonglegs]

As suggested above. If your pay periods are all 14 days then you don't really need the whole table, assuming you know the start date of the first period, and all tested dates fall within the year. Assuming your first date to test is in E2 and start date of first period in C2 you could adapt my formula above to this in F2 copied down

=INT((E2-C$2)/14)+1

If you want to use the table

=LOOKUP(E2,C$2:C$27,A$2:A$27)

[ionarodricks]

Thank you very much for helping me out.. Truly appreciate it.

Iona