Jump to content
Sign in to follow this  
Questions King

Date Calculation

Recommended Posts

Did we come in in the middle of a conversation or something? I think we need more information.

Tell us what it is you are trying to do (in greater detail), what it is, or isn't doing (in greater detail), and what you expected it to do, or not to do.

Lee

confused.gif

Share this post


Link to post
Share on other sites

Lee, I'm trying to do is:

1)Every invoice when I create new record at date of 25st to 31st, can the date field automatically change to date 01st? like I create new record at every of 25/10/2004(Date field), the(Date field) with automatically change to 01/11/2004.

2)When the date(Date field) in every month of 25st to 31st, I try to create the calculation (field) when the date field is 25st to 31st then show me the message "Please change the date to 01st".

Can this done? Lee.

Share this post


Link to post
Share on other sites

If I'm understanding you correctly, you what the results of a calculation to be based on what day of the month a date falls on. If a date is prior to the 25th of the month, the result is to be that date, and if it falls between the 25th & 31st of the month, the result should be the 1st of the following month.

I'll call your date "AnyDate" and the new calculation field c_EffectiveDate.

I have made the calculation US date format (mm/dd/yyyy) you will need to change it around for your date structure (dd/mm/yyyy).

c_EffectiveDate (Calculation, Date Result) =

Case(AnyDate >= Date(Month(AnyDate), Day(25) , Year(AnyDate)) and AnyDate <= Date(Month(AnyDate), Day(31) , Year(AnyDate)), Date(Month(AnyDate)+1, Day(1), Year(AnyDate)),

AnyDate)

Let me know if this isn't what you needed.

HTH

Lee cool.gif

Share this post


Link to post
Share on other sites

Hi Lee,

Sorry to walk on your toes Lee wink.gif

Case(Day(Date)>24;

Date(Month(Date)+1;1;Year(Date));Date)

may be a bit shorter.

Also, it still can be an autoentered date value if I understand the case

Share this post


Link to post
Share on other sites

I-Agree.gif Yours is cleaner though.

Ouch, ouch, ouch biglaff.gif

Leave it to me to make it harder than it needs to be. frown.gif

Actually, I adapted this from one of my files, and I knew either you or JT would come along and shorten it down.

I would only add one little thing for the posters benefit, since they are using v6, they may need to convert your ; to , (semicolon to colon) depending on their country version.

Lee

cool.gif

Share this post


Link to post
Share on other sites

You don't need another calculation if vs. 7. You can use Auto-Enter by calculation directly on the date field. Be sure to uncheck under 'Specify' the checkbox 'Do Not Replace Existing Values.' smile.gif

LaRetta

Share this post


Link to post
Share on other sites

Seems to be an echo in this thread.

Share this post


Link to post
Share on other sites

No echo at all, Lee. My point is that a regular auto-enter will not work unless the checkbox is unchecked for 'Do Not Replace value.' This is new in vs. 7 and would be required for the date to automatically change to the first of the next month after they enter a date. And since Questions King appears to be using vs. 7, I thought it important to clarify. smile.gif

LaRetta

Share this post


Link to post
Share on other sites

Oh man, v7??

Did they update their profile since I posted? I could have sworn that it said v6 this morning. Otherwise I wouldn't have mentioned the semicolon in my response to Ugo. In fact, I probably would have avoided it all together because of the changes in v7.

I need a vacation.

Lee

frown.gif

Share this post


Link to post
Share on other sites

Lee,

At the moment I answered, it was FM7, which explains why I suggested the auto-entered.

I thought you were just commenting for the ones who aren't using 7. smile.gif

Of course, without mentionning the "Replace existing values" option should be checked, it wouldn't have helped a lot.

Thank you LaRetta.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.