Calculating days between 3

[keo216]

I am trying to calculate the number of days between the scheduled day of a service order and the end of the month.
The formula I am using is:

Local DateVar tmpDate := {wkaw.sched_date};
(Dateadd("m",1,tmpDate)-Day(tmpDate))-tmpDate

With wkaw.sched_date being the scheduled date of the service call. The formula is in the GF.

This formulas works good for all months except if the scheduled date is Jan 31. I think the fact that Feb has 28 days is messing it up because it returns a value of a minus 3. It works fine for the last day of other months.

Can you tell me where I'm going wrong and/or is there an easier way of returning the last day of a current month?
Thanks
keo

kowen@rrwa.net

 

[kskid]

Try this

local datevar eomdate;

eomdate := dateserial(year({wkaw.sched_date}),month({wkaw.sched_date}+1,1-1)

datediff("d",eomdate,{wkaw.sched_date})

Cheeers,
-LW

 

[mbarron]

You were on the right track. Try this formula. It adds one month from the first of the month of your field(but not the number of calendar days) and subtracts 1 day.

Local DateVar tmpDate :=date(year({wkaw.sched_date}),month
({wkaw.sched_date}),1);
Dateadd("m",1,tmpDate)-1

Mike

 

[synapsevampire]

I think lw meant:

local datevar eomdate;

eomdate := dateserial(year({wkaw.sched_date}),month({wkaw.sched_date}+1,1)-1

datediff("d",eomdate,{wkaw.sched_date})

-k

 

[mbarron]

Just saw that you want haw many days were remaining. My forumla's last line should be

Dateadd("m",1,tmpDate)-1 -{wkaw.sched_date}


SV and LW's formula should read as:

local datevar eomdate;

eomdate := dateserial(year({wkaw.sched_date}),month({wkaw.sched_date})+1,1)-1

datediff("d",{wkaw.sched_date},eomdate,)

Mike

 

[keo216]

Thanks all for your replies. That worked.
FYI: I had to add a :=, a ), and a ;. It then gave me a minus number but formatted it to reverse the sign and it works fine now. Thanks again

kowen@rrwa.net

 

[kskid]

k

It's correct the way I wrote it. That's what I like about dateserial. With 1-1 in the day field, it automatically adjusts the month and day resulting in the last day of the month.

For example, if {wkaw.sched_date} = 2/15/2004
year({wkaw.sched_date}) = 2004
month({wkaw.sched_date}+1 = 3 (or March) but the 1-1 in the day field will automatically adjust the month and day properly resulting in 2/29/2004 instead of 3/0/2004 which is invalid

Cheers,

-LW

 

[keo216]

I agree with you, it worked perfectly. I copied and pasted your formula. It was just that CR wouldn't accept it till I added a ) and a ;.
I didn't mean to imply that it didn't work.
thanks for the help
keo

kowen@rrwa.net