| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Previous Pay Dates
I have a cell reference that has a begining date (for example Apr 1, 1994)
and payments should have been made on the 1st and 15 of the month every month up to the present. Is there a way a formula that can be written that way? I also need a similar formula, but the start date would be the 15th of the month. These payments will be made on the 15th and the end of the month. The end of the month could be 30, 31, 28, or 29 (depending on leap year). With this formula, we could gather totals on the other column to check to see when payments were made and keep a running total. Thank you for your help! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Previous Pay Dates
How are you wanting to handle hire dates of 7th or 18th for example?
If I guess what you want - Suppose A1 is the start date =DATE(YEAR(NOW()),MONTH(NOW()),IF(DAY(A1)<=15,1,15 )) find one of the dates =IF(DAY(A1)15,EOMONTH(NOW(),0),DATE(YEAR(NOW()),M ONTH(NOW()),15)) finds the other -- If this helps, please click the Yes button Cheers, Shane Devenshire "NFL" wrote: I have a cell reference that has a begining date (for example Apr 1, 1994) and payments should have been made on the 1st and 15 of the month every month up to the present. Is there a way a formula that can be written that way? I also need a similar formula, but the start date would be the 15th of the month. These payments will be made on the 15th and the end of the month. The end of the month could be 30, 31, 28, or 29 (depending on leap year). With this formula, we could gather totals on the other column to check to see when payments were made and keep a running total. Thank you for your help! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Previous Pay Dates
Here are slightly shorter versions of the previous formulas
=--(TEXT(MONTH(NOW()),"mmm")&IF(DAY(A1)<=15,1,15)) and =IF(DAY(A1)15,EOMONTH(NOW(),0),--(TEXT(MONTH(NOW()),"mmm")&15)) The EOMONTH is part of the ATP in Excel <=2003 so you may need to attach it- Tools, Add-ins, and check the Analysis ToolPak -- If this helps, please click the Yes button Cheers, Shane Devenshire "NFL" wrote: I have a cell reference that has a begining date (for example Apr 1, 1994) and payments should have been made on the 1st and 15 of the month every month up to the present. Is there a way a formula that can be written that way? I also need a similar formula, but the start date would be the 15th of the month. These payments will be made on the 15th and the end of the month. The end of the month could be 30, 31, 28, or 29 (depending on leap year). With this formula, we could gather totals on the other column to check to see when payments were made and keep a running total. Thank you for your help! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Previous Pay Dates
I tried the formula and it didn't work for me. I probably wasn't good at
explaining. I hope with the example below whould make better sense. I wanted the formula to adjust as I copy the cell reference in Column B and drag it below the cell until the current date appears. Let's say today's date is January 31, 2009, the last date would be January 15, 2009. Here's an example Col A Col B 1 Start Date 2 March 1, 1976 March 1, 1976 3 March 15, 1976 4 April 1, 1976 # etc.etc... # January 15, 2009 The formula in B3 above would add 14 days. I would then copy the formula in B3 and drag it to B4 and continue that process until the current date appears. The problem I found was that there are usually, 30, 31, 28, or 29 days in a month. I also was looking for another formula that does the same thing as above, but the start date would be the 15th and 1st of of every month. Hope that helps, Thank you, "Shane Devenshire" wrote: How are you wanting to handle hire dates of 7th or 18th for example? If I guess what you want - Suppose A1 is the start date =DATE(YEAR(NOW()),MONTH(NOW()),IF(DAY(A1)<=15,1,15 )) find one of the dates =IF(DAY(A1)15,EOMONTH(NOW(),0),DATE(YEAR(NOW()),M ONTH(NOW()),15)) finds the other -- If this helps, please click the Yes button Cheers, Shane Devenshire "NFL" wrote: I have a cell reference that has a begining date (for example Apr 1, 1994) and payments should have been made on the 1st and 15 of the month every month up to the present. Is there a way a formula that can be written that way? I also need a similar formula, but the start date would be the 15th of the month. These payments will be made on the 15th and the end of the month. The end of the month could be 30, 31, 28, or 29 (depending on leap year). With this formula, we could gather totals on the other column to check to see when payments were made and keep a running total. Thank you for your help! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Previous Pay Dates
For the 1st/15th condition (and assuming the date in Column A always starts on the 1st or the 15th), try these formulas...
Place In B2 ----------------- =A2 Place in B3 (and copy it down as far as needed) ------------------------------------------------------ =B2+IF(DAY(B2)=1,14,DAY(B2+3-DAY(B2+17))) -- Rick (MVP - Excel) "NFL" wrote in message ... I tried the formula and it didn't work for me. I probably wasn't good at explaining. I hope with the example below whould make better sense. I wanted the formula to adjust as I copy the cell reference in Column B and drag it below the cell until the current date appears. Let's say today's date is January 31, 2009, the last date would be January 15, 2009. Here's an example Col A Col B 1 Start Date 2 March 1, 1976 March 1, 1976 3 March 15, 1976 4 April 1, 1976 # etc.etc... # January 15, 2009 The formula in B3 above would add 14 days. I would then copy the formula in B3 and drag it to B4 and continue that process until the current date appears. The problem I found was that there are usually, 30, 31, 28, or 29 days in a month. I also was looking for another formula that does the same thing as above, but the start date would be the 15th and 1st of of every month. Hope that helps, Thank you, "Shane Devenshire" wrote: How are you wanting to handle hire dates of 7th or 18th for example? If I guess what you want - Suppose A1 is the start date =DATE(YEAR(NOW()),MONTH(NOW()),IF(DAY(A1)<=15,1,15 )) find one of the dates =IF(DAY(A1)15,EOMONTH(NOW(),0),DATE(YEAR(NOW()),M ONTH(NOW()),15)) finds the other -- If this helps, please click the Yes button Cheers, Shane Devenshire "NFL" wrote: I have a cell reference that has a begining date (for example Apr 1, 1994) and payments should have been made on the 1st and 15 of the month every month up to the present. Is there a way a formula that can be written that way? I also need a similar formula, but the start date would be the 15th of the month. These payments will be made on the 15th and the end of the month. The end of the month could be 30, 31, 28, or 29 (depending on leap year). With this formula, we could gather totals on the other column to check to see when payments were made and keep a running total. Thank you for your help! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Previous Pay Dates
For the 15th/LastOfMonth condition (and assuming the date in Column A always starts on the 15th or the last day of the month), try these formulas...
Place In B2 ----------------- =A2 Place in B3 (and copy it down as far as needed) ------------------------------------------------------ =B2+IF(DAY(B2)=15,DAY(B2+2-DAY(B2+17)),15) -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... How are you wanting to handle hire dates of 7th or 18th for example? If I guess what you want - Suppose A1 is the start date =DATE(YEAR(NOW()),MONTH(NOW()),IF(DAY(A1)<=15,1,15 )) find one of the dates =IF(DAY(A1)15,EOMONTH(NOW(),0),DATE(YEAR(NOW()),M ONTH(NOW()),15)) finds the other -- If this helps, please click the Yes button Cheers, Shane Devenshire "NFL" wrote: I have a cell reference that has a begining date (for example Apr 1, 1994) and payments should have been made on the 1st and 15 of the month every month up to the present. Is there a way a formula that can be written that way? I also need a similar formula, but the start date would be the 15th of the month. These payments will be made on the 15th and the end of the month. The end of the month could be 30, 31, 28, or 29 (depending on leap year). With this formula, we could gather totals on the other column to check to see when payments were made and keep a running total. Thank you for your help! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Previous Pay Dates
For the 15th/LastOfMonth condition (and assuming the date in Column A always starts on the 15th or the last day of the month), try these formulas...
Place In B2 ----------------- =A2 Place in B3 (and copy it down as far as needed) ------------------------------------------------------ =B2+IF(DAY(B2)=15,DAY(B2+2-DAY(B2+17)),15) -- Rick (MVP - Excel) "NFL" wrote in message ... I tried the formula and it didn't work for me. I probably wasn't good at explaining. I hope with the example below whould make better sense. I wanted the formula to adjust as I copy the cell reference in Column B and drag it below the cell until the current date appears. Let's say today's date is January 31, 2009, the last date would be January 15, 2009. Here's an example Col A Col B 1 Start Date 2 March 1, 1976 March 1, 1976 3 March 15, 1976 4 April 1, 1976 # etc.etc... # January 15, 2009 The formula in B3 above would add 14 days. I would then copy the formula in B3 and drag it to B4 and continue that process until the current date appears. The problem I found was that there are usually, 30, 31, 28, or 29 days in a month. I also was looking for another formula that does the same thing as above, but the start date would be the 15th and 1st of of every month. Hope that helps, Thank you, "Shane Devenshire" wrote: How are you wanting to handle hire dates of 7th or 18th for example? If I guess what you want - Suppose A1 is the start date =DATE(YEAR(NOW()),MONTH(NOW()),IF(DAY(A1)<=15,1,15 )) find one of the dates =IF(DAY(A1)15,EOMONTH(NOW(),0),DATE(YEAR(NOW()),M ONTH(NOW()),15)) finds the other -- If this helps, please click the Yes button Cheers, Shane Devenshire "NFL" wrote: I have a cell reference that has a begining date (for example Apr 1, 1994) and payments should have been made on the 1st and 15 of the month every month up to the present. Is there a way a formula that can be written that way? I also need a similar formula, but the start date would be the 15th of the month. These payments will be made on the 15th and the end of the month. The end of the month could be 30, 31, 28, or 29 (depending on leap year). With this formula, we could gather totals on the other column to check to see when payments were made and keep a running total. Thank you for your help! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Previous Pay Dates
Sorry... accidentally mis-posted this under your message instead of the OP's last message.
-- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... For the 15th/LastOfMonth condition (and assuming the date in Column A always starts on the 15th or the last day of the month), try these formulas... Place In B2 ----------------- =A2 Place in B3 (and copy it down as far as needed) ------------------------------------------------------ =B2+IF(DAY(B2)=15,DAY(B2+2-DAY(B2+17)),15) -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... How are you wanting to handle hire dates of 7th or 18th for example? If I guess what you want - Suppose A1 is the start date =DATE(YEAR(NOW()),MONTH(NOW()),IF(DAY(A1)<=15,1,15 )) find one of the dates =IF(DAY(A1)15,EOMONTH(NOW(),0),DATE(YEAR(NOW()),M ONTH(NOW()),15)) finds the other -- If this helps, please click the Yes button Cheers, Shane Devenshire "NFL" wrote: I have a cell reference that has a begining date (for example Apr 1, 1994) and payments should have been made on the 1st and 15 of the month every month up to the present. Is there a way a formula that can be written that way? I also need a similar formula, but the start date would be the 15th of the month. These payments will be made on the 15th and the end of the month. The end of the month could be 30, 31, 28, or 29 (depending on leap year). With this formula, we could gather totals on the other column to check to see when payments were made and keep a running total. Thank you for your help! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Previous Pay Dates
On Fri, 30 Jan 2009 13:01:02 -0800, NFL wrote:
I have a cell reference that has a begining date (for example Apr 1, 1994) and payments should have been made on the 1st and 15 of the month every month up to the present. Is there a way a formula that can be written that way? I also need a similar formula, but the start date would be the 15th of the month. These payments will be made on the 15th and the end of the month. The end of the month could be 30, 31, 28, or 29 (depending on leap year). With this formula, we could gather totals on the other column to check to see when payments were made and keep a running total. Thank you for your help! Assuming you either have the Analysis Tool Pak installed or are using Excel 2007 or later: Starting at the first pay date in the month that is in Start_Date With any date in Start_Date For 1st and 15th B2: =EDATE(Start_Date-DAY(Start_Date)+1,(ROWS($1:1)-1)/2)+14*(INT(ROWS($1:1)/2)=ROWS($1:1)/2) and fill down as far as required. For 15th and EOM: C2: =EOMONTH(Start_Date-DAY(Start_Date),(ROWS($1:1))/2)+15*(INT(ROWS($1:1)/2)<ROWS($1:1)/2) and fill down as far as required. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calculating values where it depends on the value of a previous cel | Excel Discussion (Misc queries) | |||
| Working with dates previous 1900 | Excel Discussion (Misc queries) | |||
| Count dates for previous month | Excel Worksheet Functions | |||
| Calculating monthly totals for current and previous year | New Users to Excel | |||
| Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Linear Mode
