Calculating how many days without negative numbers

[deputy95607]

I would like to make an expression showing how many days a car has been on he the lot, and break it down by the month and if a partial month just the number of days for that month and show 0 for the rest of the each month thank you

[Ted Giles]

Have you tried making the expression?
If so, give us a look please.

[madtowng]

It's relatively easy. Even easier if you're willing to use a helper column/function.
With that said, my solution is based on dates with the mm/dd/yyyy format, but that can be worked around.
As Ted suggested, it's definitely good to know more about what you've tried ( dateGone - dateArrived) should get
you extremely close to your answer depending on the details.

[deputy95607]

stordays = date()-Todaysdate
j1 = calc->Stordays-Jandays
f1 = calc->Stordays-Jandays-Febdays
m1 = calc->Stordays-Jandays-Febdays-Mardays
a1 = calc->Stordays-Jandays-Febdays-Mardays-Aprdays
ma1 = calc->Stordays-Jandays-Febdays-Mardays-Aprdays-Maydays
j1 = calc->Stordays-Jandays-Febdays-Mardays-Aprdays-Maydays-Jundays
ju1 = calc->Stordays-Jandays-Febdays-Mardays-Aprdays-Maydays-Jundays-Juldays
au1 = calc->Stordays-Jandays-Febdays-Mardays-Aprdays-Maydays-Jundays-Juldays-Augdays
s1 = calc->Stordays-Jandays-Febdays-Mardays-Aprdays-Maydays-Jundays-Juldays-Augdays-Sepdays
n1 = calc->Stordays-Jandays-Febdays-Mardays-Aprdays-Maydays-Jundays-Juldays-Augdays-Sepdays-Octdays-Novdays
d1 = calc->Stordays-Jandays-Febdays-Mardays-Aprdays-Maydays-Jundays-Juldays-Augdays-Sepdays-Octdays-Novdays-Decdays

This is the only way I know how, I am not very smart to xbasic. As you can see everything works until april then it starts in the negative
The stordays right now =117

[deputy95607]

I know I did not do something very smart I named a field todaysdate, with that being said everything is subtracting properly from 117 then in april their is only 4 days left but it puts a negative sign, and the following months should have zero or blank

[madtowng]

Hi Deputy,
It looks like you're using Todaysdate as the beginning date of storage.
I'm not sure what the purpose of j1,f1, m1 .. and so on are for, but it looks like you're missing o1.
It looks like you your stordays = 117 is considering every day of 2024 a storage day through April 28, 2024, but I don't think that's the information you're actually after.

If you don't have any of the actual coding done yet, maybe an example of the data and what you expect the results should be will give us a shove in the right direction.

Maybe this image will help with what you want to do.
image.png

[deputy95607]

the field name Todaydate should have been Towdate, saying that I take Date() - Todaysdate that give me how many days the car has been on the lot, the Todaysdate is 01/02/2024 thats gives me 117 days on the lot, now I want to break it down based on how many days on the lot per month, with that in mind j1 is january 31 day, f1 is febuary 29 days, m1 is march 31 days a1 is april 4 days, these numbers add up to 117 days, but in april it starts to give me negative numbers and on the rest of the months

[madtowng]

I'd like to see the code/page that is giving you negative numbers.
You definitely need a simple if/then to prevent negative numbers.

[Ted Giles]

You have stated the problem and how you are trying to calculate a result.
I take it that something like;

Tow Date = 21/01/2024
Jan 10 days
Feb 29 days
Mar 31 days
Apr 29 days

Sum()uIs this assumption correct?

If you have time, please humour me and let us know where this data is to be used.
Report, Screen, Email, Invoice?

It may simplify the way a calc is created.

[deputy95607]

Thank you for all your help, as I said i am not a coder. I have a field called towdate, I also have the 12 fields which are listed above they are the months of the year, each field has the numeric days of the month for example j1 is January theire is a numeric value in the field of 31 because that is how many days in that month. So if I have a car on the lot for 5 months I would need to print for each month how long the car has been on the lot, example J1 would be 31, f1 would be 29 and so on, but if a car has been only on a lot for 20 days and that month only has 30 days it would print a -20 instead of just 20 and for the rest of the months on the year it would print negative numbers instead of showing 0

[Ted Giles]

This is like pulling hens teeth. ( see my signature )
post a picture of your form, report, whatever, showing the layout and negatives.

[deputy95607]

Let me try this way, all I want to do is find out how long a car was on the lot and broke down by the month that it. Is their a expression than can do this forget about what I have

[madtowng]

This is being made far more difficult than it needs to be.
We know a car there for the full month is easy to deal with.
That means we need to deal with partial months, which have 3 scenarios.
A. Arrive after the 1st and stay the rest of the month.
B. Arrive on or before the first and leave before the end of the month.
C. Arrive after the 1st and leave before the end of the month.

I can do this easily enough (same concept as the warehousing app I have), but I have no idea what you're working with.
There are times when non-coders should pay the coders for their knowledge and get the job done.
I'm easily available most times of the day to do a quick assist/zoom/teams meeting.

[madtowng]

Deputy...look at post #6. I put all the code in there, but it might need you to tweak the way you're doing things depending on how frequently you run this app (daily, monthly, annually).

[Mike Wilson]

Hi all,
I agree it is not difficult but it is slightly complicated. There is a start date and end date (today) and the difference is the total on-lot quantity of days. The tricky part is to achieve a result that was asked, which is to have the result broken down with the quantity of days within each on-lot month. There may be an expression that can be concocted for this but I'm not confident there is and that it will be less than 1024 character in length. However a function is always available.

'RUN REPORT
dim vd_towdate as D=ctod("11/10/2023")
on_lot_report(vd_towdate)

FUNCTION on_lot_report as C (towdate as D)

dim vd_today as D=date()
dim vd_towdate as D=towdate
dim vn_mo as N=month(vd_towdate)
dim vn_yr as N=year(vd_towdate)
'msgbox("14",(""+vn_mo)+" - "+(""+vn_yr));end

dim vn_onlot_days_total as N=date()-vd_towdate
dim vn_no_of_months as N
dim vn_onlot_days_mo as N
dim vn_days_remain as N=vn_onlot_days_total

dim vd_now as D=vd_towdate
dim vd_mo_end as D

dim vc_mo as C
dim vn_mo_onlot_days as N

dim vLcol as C=""
'msgbox("28",(""+vn_mo)+crlf()+(""+vn_yr))

'DETERMINE NO MO
vn_no_of_months=round_up(vn_onlot_days_total/12,0)

FOR i = 1 to vn_no_of_months

'MAKE DATE FOR ITERATION CALCULATIONS
if i = 1
vd_now=vd_towdate
vn_mo=month(vd_now)
else
'IN AN ITERATION
vn_mo=vn_mo+1

'TEST FOR A YEAR LAPSE
if vn_mo > 12
vn_mo=1
vn_yr=vn_yr+1
end if
vd_now=ctod((""+vn_mo)+chr(47)+"01"+chr(47)+(""+vn_yr))
'msgbox("38",(""+vd_now))
end if

'GET CHAR MONTH NOW
vc_mo=left(cmonth(vd_now),3)

'GET THE NOW MONTH END DATE
vd_mo_end=month_end(vd_now)

'GET THE NUMBER OF DAYS IN THE MONTH AVAILABLE FOR ON LOT
vn_mo_days_avail=vd_mo_end-vd_now
if i > 1
vn_mo_days_avail=vn_mo_days_avail+1
end if
'msg("63",(""+vd_mo_end)+" - "+(""+vd_now)+crlf()+(""+vn_mo_days_avail)+crlf()+(""+vn_days_remain))

'TEST TO DETERMINE IF THE QUANTITY OF REMAINING DAYS EXCEEDS THE QUANTITY OF AVAILABLE DAYS IN THE MONTH
'IF THE QUANTITY OF REMAINING DAYS EXCEEDS THE QUANTITY OF AVAILABLE DAYS -> ALL THE DAYS IN THE MONTH USED AND ITERATION INTO THE NEXT MONTH NEEDED

if vn_days_remain < vn_mo_days_avail

'THIS MAKES THE EXPIRATION OF THE DAYS REMAINING AND THE END REACHED AND NO MORE ITERATIONS NEEDED
vn_onlot_days_mo=vn_days_remain
vLcol=vLcol+vc_mo+"["+(""+vn_onlot_days_mo)+"]"+crlf()
EXIT FOR
else
'THIS MAKES ANOTHER ITERATION NEEDED -> ADD THE CURRENT MONTHS ON LOT DAYS TO THE COLLECTION AND GENERATE ANOTHER ITERATION
vn_onlot_days_mo=vn_mo_days_avail
vn_days_remain=vn_days_remain-vn_onlot_days_mo
vLcol=vLcol+vc_mo+"["+(""+vn_onlot_days_mo)+"]"+crlf()
end if

NEXT

'INCLUDE TOTAL
vLcol=vLcol+"======"+crlf()+"Total= "+(""+vn_onlot_days_total)

'DISPLAY RESULTS
msgbox("Results",vLcol)

END FUNCTION​