Calculating number of days to accrue in a pay period

[superskid]

I have a payroll spreadsheet that I use to calculate my payroll accrual JE when a pay period goes over a month end. In one cell B4 I have the start date of the period (2/24/2014) and in cell B5 I have the end date of the period (3/9/2014).

Because this JE will get posted in March, I need to post an accrual in February for the February days. In this case 5 (24th - 28th inclusive)

I would like a formula that can automatically calculate the number of days to accrue if a payroll crosses over a month end. So basically it has to figure out in the range provided how many days belong to the previous month. If none then no accrual.

I've tried a few things but I'm not so good with the date formulas.

Thanks,

 

[VBA Geek]

=if(month(today())=month($b$4),0,eomonth($b$4,0)-$b$4+1)

 

[superskid]

=if(month(today())=month($b$4),0,eomonth($b$4,0)-$b$4+1)

Is the today part of the formula going to mess things up if I am behind on posting? Let's say I am just posting the first payroll in February and there are January days to accrue, but the current month is March?

 

[barry houdini]

Try this formula

=MAX(0,B5-DAY(B5)+1-B4)

 

[superskid]

Try this formula

=MAX(0,B5-DAY(B5)+1-B4)

Works perfect. Thank-you!!

 

[VBA Geek]

you mentioned previous month in your question thats why i used today


behind on posting i should speak to your boss

 

[superskid]

you mentioned previous month in your question thats why i used today


behind on posting i should speak to your boss

Hahaha....it was hypothetical, I want to make sure the formula is future proof. Or I guess in this case past proof????