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!
Solved! Go to Solution.
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.
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
@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
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!!
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!!
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!!!
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 | ||||||||