Subtracting two dates

[Beth S]

I'm having a hard time trying to subtract two dates.
Overall I'm trying to come up with a way to take a receiving date, find the wednesday of that week and then add 49 to it. So I need a date that shows 49 days from that Wednesday of that week (which the week is Sunday - Saturday). I've tried the subtracting dates and converting date to a number, but not having any luck. Seems my hands are tied with Tracerplus trying to do this calculation.
Any help would be great!!

 

[Howard Heckman III]

Hi Beth,

I have attached a project that should demo what you are trying to accomplish. The one thing you will need to do before it works is add some data to the Wednesday Offset session. This should be as follows:

Sunday 3
Monday 2
Tuesday 1
Wednesday 0
Thursday -1
Friday -2
Saturday -3

Please let me know if you have any questions on this or if I misunderstood what you were trying to accomplish.

- Howie

 

[Beth S]

This works, however, the down side is the request is that the workers don't have to put some number in cause it will just mess up the date in the long run, as many workers we are trying to limit to just scanning and submitting.

In the long run we are trying to calculate an expiration date on product. We run that product Sunday through Saturday and it has to use the Wednesday of that week and then add 49 days to it. So if someone has a way to take a receiving date and calculate the Wednesday of that week and add 49 to it, that would be awesome.

We started out thinking it would be easier to subtract two dates and use modulus with addition and subtraction to get the desired results. We have the code that we are currently using in an Access Database, but the problem we are running into is that the workers aren't hitting that button to calculate it.

I hope this makes sense. If it can't be done, then we will need to go back to the drawing board.

 

[Howard Heckman III]

Hi Beth,

The entry for those days of the week (Sunday 3, Monday 2, etc.) would only be done once by you. The users of the device would never need to enter that, it is simply seed data to perform the date calculations correctly.

Let me know if this makes sense.

 

[Beth S]

Hi Howard,
Thanks for clarifying. Been a busy week and when I looked at it the first time I didn't notice it was only put in once. Sorry about that. I believe this will do exactly what I want to do. Just got to play around with it a bit and see if suits the powers that be.

 

[Beth S]

I've been playing around with this and I came across an issue where it wouldn't pull the offset (lookup) through a live sync, so I moved it to a manual sync and it starts to work (after a refresh of the field), but my other lookup field that worked prior to that change stopped working on me. Both of these lookup fields are looking up from another session that is being pulled from an excel spreadsheet. I can view the data, but it is just not pulling it over. Any ideas on why the lookup fields aren't looking up?

 

[Beth S]

Never mind, I'm getting them to pull now. Not exactly sure what I did, but something is working right LOL