PDA

View Full Version : Date calculation and formula



Maxicus
04-11-2018, 11:35 PM
Hi

I am creating an maintenance inspection schedule userform, whereby the user can input the day the item was inspected. I am trying now to take that date entered and extend it for a specific period, whether it be monthly (30days) quarterly (90 days) or what ever the inspection frequency is required.

The dates are shown in textboxes.

I have tried the following code but it isn't working to well, because i believe it doesn't recognize the value of cur1 (textbox) as a date.

(extending the date with 30 days)

insp1.Value = cur1.Value + "30"

I have attached the workbook if that might help.

Bob Phillips
04-12-2018, 02:53 AM
It worked fine for me (your file has problems so I couldn't use that). What datatype do you have for cur1? I used Date.

p45cal
04-12-2018, 11:00 AM
try:
insp1.Value = Format(DateValue(cur1.Value) + 30, "yyyy/mm/dd")

Separately, since you're using DTPicker controls on the form, consider using them to display dates instead of the textboxes; their native data type is date. This means that you could use proper dates throughout, which in turn means that date calculations could be more straightforward, with the possiblility of using many worksheet date functions within the code, so that it (a) becomes easy to ensure dates fall on working days, and (b) month(s)/year differences wouldn't be out by a day or two (you're using day counts for all the inspection intervals).

One downside to using DTPickers is that when they're disabled so that the user can't alter them - the dates become more difficult to see (although not much more difficult than what you have at the moment). One way around this is to put them in a frame (your text boxes are already in a frame), and when that frame is disabled, the DTPickers are also disabled, but retain their enabled visibility (which you can configure anyway).
Another downside is how to display them when they contain no date; you could hide them altogether.
The upside is that you might want to allow the user to override suggested inspection dates manally, which DTPickers would allow them to do without their inputting invalid dates.