I am writing a spreadsheet with a semimonthly formula to identify a payperiod based vacation accrual . . . and I can't figure out the formula. The company pays on the 15th day and the last day of each month; and it hires employees throughout the year. This mean that I need to be able to use any of 24 starting pay period during the year. Hope this makes sense . . . and thank you for your help!
Semimonthly date formula
- Thread starter JGams
- Start date
SirJB7
Excel Rōnin
Hi, JGams!
If you can handle to auxiliary columns, you can try this:
Cell A1: first day of the month for a given period (01/01/2012)
Cell B1: 1 for first payperiod (15), 2 for second payperiod (last day of the month)
Cell C1: formula to give 15/01/2012 or 31/01/2012, depending on B1
Formula:
=FECHA(AÑO(A1);MES(A1);SI(B1=1;15;DIA(FIN.MES(A1;0)))) -----> in english: =DATE(YEAR(A1),MONTH(A1),IF(B1=1,15,DAY(EOMONTH(A1,0))))
Regards!
If you can handle to auxiliary columns, you can try this:
Cell A1: first day of the month for a given period (01/01/2012)
Cell B1: 1 for first payperiod (15), 2 for second payperiod (last day of the month)
Cell C1: formula to give 15/01/2012 or 31/01/2012, depending on B1
Formula:
=FECHA(AÑO(A1);MES(A1);SI(B1=1;15;DIA(FIN.MES(A1;0)))) -----> in english: =DATE(YEAR(A1),MONTH(A1),IF(B1=1,15,DAY(EOMONTH(A1,0))))
Regards!
SirJB7,
That worked great! Building on this is there a way to populate the remaining payperiod dates for the balance of the year? Essentially auto loading the following paydate? (e.g. - if the initial computation was for 01/15/2012 can I automatically populate subsequent cells for 01/31/2012, 02/15/2012, etc . . .?) and is there a way to drop the year designatir from displaying?
Thanks again!
That worked great! Building on this is there a way to populate the remaining payperiod dates for the balance of the year? Essentially auto loading the following paydate? (e.g. - if the initial computation was for 01/15/2012 can I automatically populate subsequent cells for 01/31/2012, 02/15/2012, etc . . .?) and is there a way to drop the year designatir from displaying?
Thanks again!
SirJB7
Excel Rōnin
Hi, JGams!
Check this file:
http://www.2shared.com/file/hwyllLQs/Semimonthly_date_formula__for_.html
I'm getting lazier, I guess, but let's translate.
Row 1, same as previous. Column C, same as previous
A2: =SI(B2=2;A1;FECHA(AÑO(A1);MES(A1);DIA(FIN.MES(A1;0))+1)) -----> in english: =IF(B2=2,A1,DATE(YEAR(A1),MONTH(A1),DAY(EOMONTH(A1,0))+1))
B2: =SI(B1=1;2;1) -----> in english: =IF(B1=1,2,1)
Copy A2:B2 to A3:B24 and C1 to C2:C24.
If you can change B formula to something like =2-MOD(ROW(),2) if starting on an odd row, or like =MOD(ROW(),2)+1 if starting on an even row.
Regards!
Check this file:
http://www.2shared.com/file/hwyllLQs/Semimonthly_date_formula__for_.html
I'm getting lazier, I guess, but let's translate.
Row 1, same as previous. Column C, same as previous
A2: =SI(B2=2;A1;FECHA(AÑO(A1);MES(A1);DIA(FIN.MES(A1;0))+1)) -----> in english: =IF(B2=2,A1,DATE(YEAR(A1),MONTH(A1),DAY(EOMONTH(A1,0))+1))
B2: =SI(B1=1;2;1) -----> in english: =IF(B1=1,2,1)
Copy A2:B2 to A3:B24 and C1 to C2:C24.
If you can change B formula to something like =2-MOD(ROW(),2) if starting on an odd row, or like =MOD(ROW(),2)+1 if starting on an even row.
Regards!