Income for overlapping months to be split daily...

See attached file, where I have copied your data from columns A:D into a new sheet then set up some formulae in columns F -J to split the days for each month, and then it's just a simple apportionment in columns M - Q.

Hope this helps.

Pete

Pete.. you are a star. Took me 10 mins to do an analysis that used to take 2 hours.
SDCH... thanks for your contribution as-well.. its much cleaner.. but I will be using Pete's as it shows split calculations; if something goes wrong i can identify any errors.

I have another query. Let say that instead of just the date I also have the time: As follows:

FROM--31/12/2013--13:00:00--TO--07/01/2014--17:00:00--8 days

Obviously actual calculations is 7 days plus 4 hours; but the system would be charging 8 full days. With your (Pete/SDCH) formulas, it obviously picking up 7 days. But i would need it to pick 8 days as per the Number of days column. Is there a 'restriction that can be made... that the number of days split between the months; must equal this column.
In the case above I would have 1 day in December and 7 days in January. I don't actually need to use the time... but explained it to you for simplicity sake.

Many thanks
Sam

Are your times in different columns, or do you have date/times in column A and B?

If you were going from, say, 16:00 on one day to 12:00 on the next day, would this count as 1 day? And if the start date were the last day of the month and the end date was the first of the next month, how would you like that to be apportioned?

Please post another example workbook with date/times, showing how you would like those to be apportioned.

Pete

Hi Pete.. Managed to arrange it in the meantime. Thanks. apportioned in previous month.
Thanks
Sam

i want to formula in excel vb code "sumifs"
check attecment
sheet1 me data hai or sheet2 me "amt" column me "sumifs" ka formula hai woh vb code me dena hai , sheet1 data hai to sheet2 me date or name
likhne par amout ani chahiye
ye attechment me jo excel sheet hai woh sirf dikhane ke liye hai to sheet me formula sheet1 or two me dena hai
plz help me

@sandip46:

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Pete

Originally Posted by pete_uk

@sandip46:

Unfortunately your post does not comply with rule 2 of our forum rules. do not post a question in the thread of another member -- start your own thread.

if you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Pete

ok and how to open new thread

From the home page (click Forum at the top of the screen), click on the sub-Forum that you want to post in (e.g. Excel Formulas and Functions) then click on Post New Thread.

Hope this helps.

Pete

Hi Pete... Thanks for your reply.

Can you send the UDF file here please. (previously pm)

Regards
Sam

Sorted... created the module.