Travel Date Mapping with Billing Date

[Swapnil]

Greetings !!!!!!

This is a recent problem I came across. I have two sheets here , One containing employee bill details ( along with desired Output ) named " Employee Billing Data". While the other have their travel details along with Geography in which they traveled named "Travel Data".

I need to find out how many of their travel days fall within a particular billing cycle , and need to populate the same in respective geographies.

attaching the file for ready reference. Looking forward to an Awsome solution from Awsome excel gurus.

 

[Luke M]

Hi Swapnil,

I was able to come up with a solution using a UDF. See attached.
One small thing to note...I am not sure your example numbers were correct, and I think it's related to how dates were counted. If I have a trip from Jan 11 - Jan 17, your subtraction formula counted this as 6 (17 - 11 = 6). However, since the dates are inclusive, this should actually be a duration of 7 (a full week).

 

[Swapnil]

Thanks Luke,

I intently excluded one date as I did not wanted to include the same for my calculation purpose as a nature of the analytics.

By d way If there a way through which I can address my problem through mix of existing (Inbuilt) functions?? ( Just wanted to know ).

Hi Swapnil,

I was able to come up with a solution using a UDF. See attached.
One small thing to note...I am not sure your example numbers were correct, and I think it's related to how dates were counted. If I have a trip from Jan 11 - Jan 17, your subtraction formula counted this as 6 (17 - 11 = 6). However, since the dates are inclusive, this should actually be a duration of 7 (a full week).

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Luke M]

Easy enough to switch the UDF, if you want. It would change this line

Set newRange = Range("A" & CLng(StartRange.Cells(xCounter).Value) & ":A" & CLng(EndRange.Cells(xCounter)))

to this

Set newRange = Range("A" & CLng(StartRange.Cells(xCounter).Value) + 1 & ":A" & CLng(EndRange.Cells(xCounter)))

Up to you whether to use the UDF of Narayan's solution.