cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Leu0101
Helper III
Helper III

Expiration date creates a due date that falls into a group of due dates beginning of the year or middle of the year.

This may be a little confusing as it has been for me so please bare with me. I have an expiration date and want it to automatically create a due date that falls into either the 12/30 end of year group or 6/30 middle of the year group.

 

As an example:

I have a record with a expiration date of "01/01/2023".  I want it to fall into the group of 06/30/2022 as 12/30/2022 would be to close of a due date. Id like at least a few month gap in-between dates so it gives us preparation time.

 

I'm using a date picker for the due date and a date picker for the expiration. When I pick the expiration date I'd like it to auto fill the due by date.

 

Here is the code I'm currently playing with. Not the best written as I'm sure there is something better out there.

This code would'nt work due to the year change and not giving me a few months gap. Pretty embarrassing, lol. Hard to wrap my head around this.

 

If(Value(DateValue(ThisItem.CertExpiration),"[$-en-US] mm/dd/yyyy") < Value(DateValue("06/29"),"[$-en-US] mm/dd") , Text(DateValue("6/30/" & Text(DateValue(ThisItem.CertExpiration),"[$-en-US] yyyy") ),"[$-en-US] mm/dd/yyyy"),Text(DateValue("12/30/" & Text(DateValue(ThisItem.CertExpiration),"[$-en-US] yyyy") ),"[$-en-US] mm/dd/yyyy"))

 

3 REPLIES 3
Roverandom
Solution Supplier
Solution Supplier

how about using the DateDiff function?



Hi! I hope I was helpfull. Please always mark the answer that helped you, so others who need it will find it faster.



Roverandom is a book by J.R.R Tolkien. I'm a big fan of his works and as Frodo, Aragorn or Gollum are already widely used, I decided to use this nickname.

@Roverandom Could you possibly give me an example. I took another shot at it yesterday with no luck. I'm gonna take another shot at it today.

Roverandom
Solution Supplier
Solution Supplier

Here's an example of how you can determine the number of months between dates (don't worry about Button or Set.. etc):

 

Roverandom_0-1662052935369.png

 



Hi! I hope I was helpfull. Please always mark the answer that helped you, so others who need it will find it faster.



Roverandom is a book by J.R.R Tolkien. I'm a big fan of his works and as Frodo, Aragorn or Gollum are already widely used, I decided to use this nickname.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (2,558)