Have a column of dates (first of each month) and need a function to add a number if the first of the month falls between two dates

danielrussell2

New Member
Joined
Mar 17, 2016
Messages
17
I am trying to set up an excel to show income and expenses for each period, on a two week pay schedule.

I have the beginning date of the pay period in column A (starting at A8), the end of the pay period in column B (starting at B8). In column C I am trying to show what the income will be for that pay period...of course the salary pay will be the same for each pay period, but there's also once a month incomes to consider. The one I'm trying to add right now is paid the first of each month. So in a column over to the side (column Y), I made a list of all of the first of the month dates (06/01/18, 07/01/18, 08/01/18, etc.). I need a function that will add the amount of the once a month income (listed in cell C3) IF the date in column Y (the first of the month) falls within the date range in columns A and B (both start at row 8). I made a table below to reflect what my excel sheet looks like. Thanks in advance for your help!

ABCD....Y
1
22 week salaryOnce a month
3Amount
1000200
4Day of Month1
5
6Monthly
7Pay DatePeriod EndIncome06/01/18
805/2506/07=B3+?????07/01/18
906/0806/2108/01/18
1006/2207/0509/01/18
1107/0607/1910/01/18
1207/2008/0211/01/18
1308/0308/1612/01/18
1408/1708/3001/01/19

<tbody>
</tbody>
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

danielrussell2

New Member
Joined
Mar 17, 2016
Messages
17
figured out another way to do this not using the Y column I set up:

=$B$3+(if(A8<=(B8-DAY(B8)+1),if(B8>=(B8-DAY(B8)+1),$C$3,0),0))
 

Forum statistics

Threads
1,181,642
Messages
5,931,172
Members
436,780
Latest member
doknights

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top