Dates between two dates within one month

[MKM]

Hi all, 

Couldn't find this answer but seems doable

I want to count the amount of days within one month between two dates. For example, if the start date is 8/1/2017 and the end date is 10/31/2017,  I want to return 31 because there are 31 days in August and when I run it again in September, I'd want it to return 30.

I tried using DATETIMEDIFF but that didn't include the part of days within a certain month. Is that doable?

Thanks!

[rohanonline]

The datetimediff should work. The exact formula to use is -

DateTimeDiff("2017-08-31","2017-08-01","days")+1

The '+1' in the end is to include the starting date. You may also replace the dates with variables that are of "date" type.

[MKM]

Thanks for the reply rohanonline! 

I updated the formula to include the +1 but it still seems to be including the entire range. In your example below, you have the two dates as within the same month. If the two dates are in different months (which is what I have), should that do the same?

An example from my data set is: start date 8/1/2017 & end date 10/25/2017

What I'd like returned is:

When I run the workflow in August, return 31

When I run the workflow in September, return 30

When I run the workflow in October, return 25

[rohanonline]

@MKM - It seems I understood the question incorrectly earlier.

What I understand now is that you will have 3 dates - start date, end date and run date.

Hence, in the example,

start date = 8/1/2017

end date = 10/25/2017

Then if, run date = 8/10/2017, the answer should be 31

if, run date = 9/10/2017, the answer should be 30

if, run date = 10/10/2017, the answer should be 25

if, run date = 11/10/2017, the answer should be 0

If this is correct, then please try using the attached workflow. Do let me know if it works or not. Thanks

[MKM]

Thanks for the attachment! Just reading the formula you added, I think it should work. However, I'm noticing that I can't add the date column name in place of the "Start" and "End" in your formula. Is it possible to use this formula while referring to a column field where all the values in the column are dates as oppose to a particular date value? 

Thank you again for all the help rohanonline!!

[rohanonline]

I am tempted to say that it should work fine. Not sure why you can't add the date column name. It would be better if you can share your workflow along with the sample data so that I can help better.

[MKM]

Here's a sample of the date structure in our data (I can't send the actual file since it contains client information) 

Start Date	End Date	Days in August
8/1/2017	10/31/2017	31
8/1/2017	10/31/2017	31
10/1/2017	12/31/2017	N/A
8/9/2017	12/31/2017	23
6/1/2017	8/8/2017	8
8/1/2017	8/9/2017	9
8/10/2017	12/31/2017	22

What I was hoping to do was use the formula you mentioned, but reference the "Start Date" and "End Date" column headers so that I can apply it to each row. I'm pretty sure your formula would do the same thing, output-wise, as what I have in the third column, though I'm not sure it includes the "N/A" portion when there are no days in that month. 

Thanks again!!

[rohanonline · Accepted solution]

Please see the attached. This gives the exact result as you have mentioned in the table below.

[MKM]

Thanks rohanonline!!! I think this worked. For those that come back n/a, it seems like it flipped the effective start & end date. Any idea why? Doesn't really impact the result, just curious. 

Thank you again!!!

[syatham1]

Hi, I created a reportdate_id for unique report date, I want to generate dates between every two unique report dates and load it in to a different table. I am thinking to create a macro to do that but not sure how. Can anyone help me with that ?

---

 reportdate_id 

report_id	reportdate		Newtable
1	9/13		report_id
2	9/15		1	9/13
3	9/17		1	9/14
			2	9/15
			2	9/16
			3	9/17