Excel formula question

[josh0227]

I am making a budget for myself, and some of my co-workers.
On the spreadsheet, under pay period/paycheck information I am trying to find a formula that will automatically fill in the date, two weeks from the first date entered. For example: If the first pay period lands on 1/1, I would like the next cell down to autofill to 1/14 (for the next pay period ending date). The catch is that I have a spreadsheet for every month of the year within the excel file, so for example in January, if we get paid on 1/1, 1/14 and 1/28, I would like the equation to be smart enough to know that there is not another pay period in that month so it would not automatically autofill the next cell down to 2/11 (for the next pay period). Instead, that pay period (2/11) would need to be on the February sheet, which brings me to my next question.... is there a formula that will do the math so that each months sheets, jan, feb, march, etc... will autofill once I enter the first pay period date in January?
I know the equation =(cellletter#+14), but I am sure that there is something else that I could do in reference to the above questions.
Please help!!!

Thanks and sorry for the long post.

 

[Gavona]

How about
=if(round((thisdate-firstdate)/14,0)=(thisdate-firstdate)/14,"its payday","")

Replace 'thisdate' and "firstdate" with cell references. firstdate should be absolute

basically this will check that there are exactly a multiple of 14 days between the two dates.



Thanks,

Gavin

 

[byundt]

Here is a workbook with twelve worksheets and a user-defined function (UDF) that returns the date of each pay period. The UDF assumes that each successive column is for the next pay period, and that each successive worksheet is for the next month.

A sample workbook showing how it all works is at

http://home.mchsi.com/~byundt/PayPeriodsTT1070003.xls

The code for the UDF goes in a regular module sheet. If is used with a worksheet formula like:
=PayDay(FirstPayDay)
In this formula, FirstPayDay is a named range for the cell on the first worksheet that contains the date of the first pay day. Because the UDF knows which worksheet and which column is calling it, the UDF can calculate which pay period is required.

Function PayDay(FirstDay As Range) As Variant
Dim col As Long, FirstCol As Long, i As Long, j As Long, k As Long, kk As Long
Dim EOlastMonth As Date
FirstCol = Columns("B").Column      'First pay period in month goes in this column, with subsequent pay periods in next columns
i = FirstDay.Parent.Index
j = Application.Caller.Parent.Index
k = Application.Caller.Column - FirstCol   'First paydate goes in colummn B
EOlastMonth = DateSerial(Year(Date), j - i + Month(FirstDay), 1) - 1
If i <> j Then kk = Int((EOlastMonth - FirstDay) / 14) + 1      'Number of pay periods through end of last month
PayDay = FirstDay + (kk + k) * 14
If Month(PayDay) - Month(FirstDay) <> j - i Then PayDay = ""
End Function

To install a function in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

Optional: to add descriptive text (appears at bottom of Function Wizard):
5) ALT + F8 to open the macro window
6) Type in the name of your function in the "Macro name" field at the top
7) Press the "Options" button
8) Enter some descriptive text telling what the function does in the "Description" field
9) Click the "OK" button

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Brad

 

[Gavona]

Brad's solution is great. However if you want to avoid code:

A slightly simpler variation on my suggestion is.

=IF(MOD(A11-FirstPayDay,14)=0,"payday!","")

This is the formula in B11. I am assuming here that you have a list of dates in column A and want to identify the paydays.

On the other hand if you just want a list of pay dates then, with the first day of the month in cell(A1)

=$A$1+14-MOD($A$1-FirstPayDay,14)

Gives the first pay day. And, assuming that the above is in cell A13, then in A14 type

a13+14-MOD(a13-FirstPayDay,14)

will give the next pay date. Copy this second formula down the spreadsheet. But you would need to add a test to make sure that the date was within the current month..(I would suggest using IF and the EOMONTH function for this) ...but you will probably prefer Brad's approach.


Thanks,

Gavin

 

[xlbo]

Good advice there but a general piece of advice.

Don't split up your data.

This is a very common mistake and leads to all kinds of nasty manipulation when it comes to trying to put it together again. you would almost certainly be better off having ONE sheet for all your data and calculations and a seperate sheet to use or display data. As soon as you split things up, it becomes MUCH harder to do anything with.

Rgds, Geoff

Well, he's kind of had it in for DPlank ever since he accidentally ran over his dog. Actually, replace "accidentally" with "repeatedly," and replace "dog" with "son."

Please read FAQ222-2244 before you ask a question

 

[josh0227]

Wow! Well, the programming post is way beyond my scope of knowledge. I sort of understand the other stuff, however I am trying to link the other cells in other workbooks. I don't have a list of dates on each workbook. I have 4 cells on each book... Jan, Feb, March, etc... that look like this:


Date Paid From Amount
----------------------------------
- - -
----------------------------------
- - -
----------------------------------
- - -
----------------------------------

Where the cells under date are A3, A4, A5...under paid from are B3, B4, B5, etc....

In the June workbook, if A3 is 06/08/05, I would like A4 to automatically show 06/22/05, then in the July workbook in cell A3, it would show 07/06/05 (but it would not show the July paydate in the June workbook). Since I am new to this, what would be the formula to do so, and how do I link the formulas to the July, Aug. Sept, etc.. workbooks so they all do the same thing?

I really appreciate your help.

 

[byundt]

The UDF that I wrote is assuming that all the worksheets are formatted the same and that they are all in the same workbook. If you put dates in different cells in different worksheets, then it isn't going to work.

As an alternative, how about linking the formulas in one worksheet/workbook to the others?

To link to a cell on another worksheet in the same workbook:
=MAX('Other worksheet'!A3:A5)+14
This formula grabs the last date in A3:A5 and adds 14 days to it.

To link to a cell in another workbook:
=MAX('[Another workbook.xls]Other worksheet'!A3:A5)+14

You'll need to put the actual names of the worksheets and workbooks in these formulas to get them to work.

Brad

 

[Gavona]

I assume that you do have a cell in the sheet that specifies the month. To use my formulae then let us assume that the month is in cell A1. It should actually be the date of the first of the month but you can format it to just display the month, or month and year if that is what you want.

Somewhere, on any sheet in the workbook put the first pay date and name this range "FirstPayDay"

in cell A3 put

 =$A$1+14-MOD($A$1-FirstPayDay,14)

That will give you the first pay day in the month.

in cell A4 put

=IF(A3="","",IF(EOMONTH($A$1,0)>=A3+14,A3+14,""))

copy this down to cell A5.

I think that should do the job for you.

Thanks,

Gavin