[SOLVED]Find a closer date for payroll in a range of date

**jackson_hollon** · 10-05-2014, 08:58 PM

Hello,

I have a range of monthly date in Column B1 to G1. Every month we have to pay out $150,000 (in Row 2). In row 9, I have a range of weekly date. we are making payroll on the 5th and 20th of every month. I am looking for a formula that can populate if c11 is the payroll week (this week has 20th, therefore, it is payroll week) in row 1, it will assign 50% of correspond salary is under the month of row 1.

I hope I am making myself clear.

Thanks for the help.

**boopathiraja** · 10-05-2014, 11:23 PM

Hi, refer the attachment wit formulas

**jackson_hollon** · 10-05-2014, 11:51 PM

boopathiraja,

Thank you very much for the help. However, the formula seems have problem. First of all, there is no May in row 1, but in row 10, it picked up a value for May. Second, in O10, the Sep 29 payroll is missing. I used the same formula, but didn't work. Could you please take a look again?

Thank you very very much for your help.

Monthly and Weekly V2.GIF

**samba_ravi** · 10-06-2014, 02:04 AM

can you attach sample file with desired result

**jackson_hollon** · 10-06-2014, 04:09 AM

nflsales

I have attached the file here. Thank you in advance for all of your help.

efb.xlsx

**samba_ravi** · 10-06-2014, 05:58 AM

=IF(OR(AND(DAY(C9+2)<=5,DAY(C9+7)>5),AND(DAY(C9)<=5,DAY(C9+7)>5),AND(DAY(C9)<=20,DAY(C9+7)>20)),LOOKUP(C9-7,$B1:$N1,$B4:$N4),0)
TRY THIS FORMULA IN Cell "C12" and copy towards right

**jackson_hollon** · 10-06-2014, 07:47 AM

nflsales,

Thanks for the help. I tried the formula, it works great except the entire Dec 15 is off by 75,000. Could you please check?

Thank you very much again.

**samba_ravi** · 10-06-2014, 07:57 AM

as per data in your sheet Dec-15 showing an amount of Rs 470,764.26 in that half of the amount is Rs. 235382.13
the formula is showing the same result
i don't know how you got the 310382.128254075

**jackson_hollon** · 10-06-2014, 10:24 AM

nflsales

You are right. I just found my mistake.

Thank you very much for the help.

**samba_ravi** · 10-06-2014, 11:06 AM

You are welcome,

If your question is resolved, mark it SOLVED using the thread tools .and ad.d reputation by click on the star if you think it helped you

[SOLVED]Find a closer date for payroll in a range of date

LinkBack

Thread Tools

Rate This Thread

Display

[SOLVED]Find a closer date for payroll in a range of date

Re: Find a closer date for payroll in a range of date

Re: Find a closer date for payroll in a range of date

Re: Find a closer date for payroll in a range of date

Re: Find a closer date for payroll in a range of date

Re: Find a closer date for payroll in a range of date

Re: Find a closer date for payroll in a range of date

Re: Find a closer date for payroll in a range of date

Re: Find a closer date for payroll in a range of date

Re: [Solved]Find a closer date for payroll in a range of date

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] look-up a date in a range and find the date that is closest todays date

[SOLVED] How do you make cells change color as the indicated date come closer?

Automatic mail on due date (30 days or closer)

Given an end date find corresponding start date 3 months prior ..quarter date range

Payroll - Combine SUMPRODUCT, SUMIF, COUNTIF within date range

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions