• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

dates

M

Manuel998

Member
Hi

I have a date range e.g. 11/08/2016 - 08/02/2017, I need a formula that can split these dates into 4/5 weekly periods of the month so say e.g. it should be split out as follows:

11/08/2016 - 21/08/2016
22/08/2016 - 25/09/2016
26/09/2016 - 23/10/2016
24/10/2016 - 20/11/2016
21/11/2016 - 25/12/2016
26/12/2016 - 22/01/2017
23/01/2017 - 08/02/2017

I would appreciate your help greatly as this is currently a very manual task and it would be great to try and solve this.

Thanks!
 
I need to use this to allocate the correct pay for each period.

the longest period will be spread across over a year so say e.g. the intern could be employed from 01/01/2017 - 31/12/2017(longest period)
 
Hi ,

The logic is still not clear ; if the start date is 11-08-2016 , why does the first period have an end date of 21-08-2016 ? Why not some other date ?

Narayan
 
this is because the pay periods for payroll are as follows:
25/07/2016 - 21/08/2016
22/08/2016 - 25/09/2016
26/09/2016 - 23/10/2016
24/10/2016 - 20/11/2016
21/11/2016 - 25/12/2016
26/12/2016 - 22/01/2017
23/01/2017 - 19/02/2017

I have then got a formulae to prorate the cost based on the start date and pay period, hope this makes sense
 
yes, I have had a think about this and I think the best way is as you say to list the pay periods for the entire year and apply an if & vlookup formula to specify the pay period, so say e.g. If X had a start date of (11/08/2016) and an end date of (19/02/2017) then the formula could look up all the interns that fall within that pay period based on the start and end dates. any ideas of how I can structure this formula?
 
by the way here are the pay periods:
Pay Period

Weeks commencing

Week ending

Oct-16

26/09/2016

21/10/2016

Nov-16

24/10/2016

18/11/2016

Dec-16

21/11/2016

23/12/2016

Jan-17

26/12/2016

20/01/2017

Feb-17

23/01/2017

17/02/2017

Mar-17

20/02/2017

24/03/2017

Apr-17

27/03/2017

21/04/2017

May-17

24/04/2017

19/05/2017

Jun-17

22/05/2017

23/06/2017

Jul-17

26/06/2017

21/07/2017

Aug-17

24/07/2016

18/08/2017

Sep-17

21/08/2017

22/09/2017
 
Hi ,

Do you happen to have this data in a workbook ? Otherwise , I will have to copy + paste and then re-arrange to get it into a normal tabular format.

Narayan
 
the section from A16 onwards is what it should look like if it was done manually
 

Attachments

  • Interns (test).xlsx
    12 KB · Views: 4
Narayank,

I have updated the spread sheet slight and thank you ever so much for helping me out with this, my final question(probably) would be say e.g on the record sheet if I added new interns how would the formula get updated because when I try to drag this down in the pay spread sheet this seems to give me errors.Once again thank you for your help on this.
 

Attachments

  • Interns (test).xlsx
    14.8 KB · Views: 2
Hi,

Please go through the enclosed work book. Hope its solves your problem.

with best regards,
Arun N
 

Attachments

  • Interns (test).xlsx
    26.1 KB · Views: 3
Hi,

I have got a small issue with the spread sheet above;
Say for example in the records sheet if I change the date to an earlier date the data in Pay period 2 moves out of sync, is there any way of stopping this from happening? I would greatly appreciate your help
 
Hi Narayank,

if you look at anelise record e.g when I change the date for her to finsh earlier the lines go out of sync.appreciate any help you can provide (results are in tab Pay2)

Thanks
 

Attachments

  • Interns (test).xlsx
    25 KB · Views: 4
Hi ,

I am sorry but I am not able to understand what you mean by lines going out of sync.

Can you explain with specific reference to the data in the worksheet ?

What should be the correct results , and what are the results you are getting at present ?

Narayan
 
Hi Narayan,

Is it ok if I try something out before sending this across because I have managed to identify the problem, I will also let you know what I did so there is a record on the forum if anyone needs to refer to this.

Thanks
Manuel
 
You must log in or register to reply here.
Back
Top