XLSForm Date Calculations: Add Days, Months, Due Dates

To add days to a date in XLSForm, write date(decimal-date-time(${lmp_date}) + 280) in the calculation column of a calculate row. To count the days between two dates, subtract them: int(${discharge_date} - ${admission_date}). Calendar months need the year, month and day parts from format-date(), because months are not all the same length. Every example on this page compiles with pyxform and ODK Validate, the same check ODK Central and KoboToolbox run on upload.

Download example form (.xlsx)All seven examples in one XLSForm, one group each. Tested with pyxform and ODK Validate; free to reuse under CC BY 4.0.Download

How do I add days to a date?

Convert the date to a number of days, add to it, and convert back. This works out an expected date of delivery as 280 days after the first day of the last menstrual period:

type	name	label	required	constraint	constraint_message	calculation
date	lmp_date	What was the first day of her last menstrual period?	yes	. <= today() and . > today() - 310	The date must be within the last 310 days.
calculate	edd					date(decimal-date-time(${lmp_date}) + 280)
calculate	edd_text					format-date(${edd}, '%e %b %Y')
note	show_edd	Expected date of delivery: ${edd_text}

◀ ▶surveychoicessettings+

Two functions do the work, and they are the inverse of each other:

• decimal-date-time(date) gives the number of days since 1 January 1970.
• date(days) turns a number of days back into a date, stored as YYYY-MM-DD.

For weeks, add a multiple of 7: a follow-up visit in two weeks is date(decimal-date-time(${visit_date}) + 14). The second calculate here is only for the screen. format-date() shows the date as 21 Aug 2027; the codes are in date format in XLSForm.

How do I subtract days from a date?

Use the same pattern with a minus sign. A common use is the start of a recall period, so the enumerator can read out a real date instead of "in the last 30 days":

type	name	label	required	constraint	constraint_message	calculation
today	today
calculate	recall_start					date(decimal-date-time(${today}) - 30)
calculate	recall_start_text					format-date(${recall_start}, '%e %b')
integer	clinic_visits	How many times has anyone in the household visited a health facility since ${recall_start_text}?	yes	. >= 0 and . <= 60	Enter a number from 0 to 60.

◀ ▶surveychoicessettings+

The today row is a metadata question. It records the date the form was first opened and is never shown, so the recall period does not move when the form is edited a week later. today() with brackets is recalculated every time the form is opened.

How do I count the days between two dates?

Subtract the earlier date from the later one. Two date questions subtract directly, and the result is a number of days:

type	name	label	required	constraint	constraint_message	calculation
date	admission_date	On what date was the child admitted to the programme?	yes	. <= today()	The admission date cannot be in the future.
date	discharge_date	On what date was the child discharged?	yes	. >= ${admission_date} and . <= today()	The discharge date must be on or after the admission date.
calculate	stay_days					int(${discharge_date} - ${admission_date})
note	show_stay	Length of stay: ${stay_days} days

◀ ▶surveychoicessettings+

A child admitted on 3 March and discharged on 17 April has a stay of 45 days. The subtraction counts nights, not calendar days touched: admitted and discharged on the same day gives 0. If your indicator counts both the first and the last day, add 1.

To count the days from a date until today, use int(${today} - ${visit_date}) with the today metadata row. More ways to restrict the dates themselves are in date constraints in XLSForm.

How do I get weeks and days between two dates?

Divide the days by 7 with div and keep the whole part with int(). The remainder, mod 7, is the extra days. This shows gestational age the way a midwife reads it:

type	name	label	required	constraint	constraint_message	calculation
today	today
date	lmp_date	What was the first day of her last menstrual period?	yes	. <= today() and . > today() - 310	The date must be within the last 310 days.
calculate	ga_days					int(${today} - ${lmp_date})
calculate	ga_weeks					int(${ga_days} div 7)
calculate	ga_extra_days					${ga_days} mod 7
note	show_ga	Gestational age: ${ga_weeks} weeks and ${ga_extra_days} days

◀ ▶surveychoicessettings+

A last period that started 200 days ago gives 28 weeks and 4 days. Keep ga_days in the data: it is the exact value, and the other two are derived from it.

How do I count the months between two dates?

Do not divide days by 30. Months have 28 to 31 days, so the result drifts by a month every few years. Count calendar months from the year, month and day parts instead. %Y is the year, %n the month number and %e the day of the month:

type	name	label	required	constraint	constraint_message	calculation
today	today
date	arrival_date	On what date did the household arrive in this camp?	yes	. <= today()	The arrival date cannot be in the future.
calculate	months_in_camp					(int(format-date(${today}, '%Y')) - int(format-date(${arrival_date}, '%Y'))) * 12 + int(format-date(${today}, '%n')) - int(format-date(${arrival_date}, '%n')) - if(int(format-date(${today}, '%e')) < int(format-date(${arrival_date}, '%e')), 1, 0)
note	show_months	The household has been in the camp for ${months_in_camp} completed months.

◀ ▶surveychoicessettings+

The formula reads: whole years times 12, plus the difference in months, minus one if the day of the month has not been reached yet. A household that arrived on 20 August 2025 and is interviewed on 14 November 2026 has been there 14 completed months (12 + 3 - 1). For completed years, take int(${months_in_camp} div 12). The same formula with a date of birth gives age, covered in calculate age from date of birth.

How do I add months to a date?

There is no EDATE() in XLSForm. Adding 30 days per month lands on the wrong day, so add to the month number and build the new date as text. This works out when a loan falls due, from the disbursement date and the term in months:

type	name	label	required	constraint	constraint_message	calculation
date	loan_date	On what date was the loan disbursed?	yes	. <= today()	The disbursement date cannot be in the future.
integer	loan_term	What is the loan term in months?	yes	. >= 1 and . <= 60	The term must be between 1 and 60 months.
calculate	month_index					int(format-date(${loan_date}, '%Y')) * 12 + int(format-date(${loan_date}, '%n')) - 1 + ${loan_term}
calculate	due_year					int(${month_index} div 12)
calculate	due_month					${month_index} mod 12 + 1
calculate	due_month_days					if(${due_month} = 2, if(${due_year} mod 4 = 0 and (${due_year} mod 100 != 0 or ${due_year} mod 400 = 0), 29, 28), if(${due_month} = 4 or ${due_month} = 6 or ${due_month} = 9 or ${due_month} = 11, 30, 31))
calculate	due_day					if(int(format-date(${loan_date}, '%e')) > ${due_month_days}, ${due_month_days}, int(format-date(${loan_date}, '%e')))
calculate	due_date					if(${loan_date} = '' or ${loan_term} = '', '', concat(${due_year}, '-', if(${due_month} < 10, '0', ''), ${due_month}, '-', if(${due_day} < 10, '0', ''), ${due_day}))
note	show_due	The loan falls due on ${due_date}.

◀ ▶surveychoicessettings+

Row by row:

• month_index counts months from year 0 and adds the term, so a term that crosses a new year needs no special case.
• due_year and due_month split that count back into a year and a month from 1 to 12.
• due_month_days is the length of the target month, with the leap year rule for February.
• due_day keeps the original day of the month, or the last day of the target month when it is shorter.
• due_date joins the parts as YYYY-MM-DD. The if(... < 10, '0', '') parts add the leading zero that a date needs.

A loan disbursed on 30 November 2026 with a 3-month term falls due on 2027-02-28, not on 2 March. To go back in time, subtract from month_index instead of adding. The text in due_date has the same form as a date question's value, so decimal-date-time(${due_date}) reads it for further arithmetic.

How do I check whether a due date has passed?

Keep the due date as a number of days, and compare it with today's number. This flags a late second vaccine dose, due 28 days after the first:

type	name	label	required	constraint	constraint_message	relevant	calculation
today	today
date	dose1_date	On what date did the child receive the first dose?	yes	. <= today()	The date cannot be in the future.
calculate	dose2_due_days						decimal-date-time(${dose1_date}) + 28
calculate	dose2_due_text						format-date(date(${dose2_due_days}), '%e %b %Y')
calculate	days_overdue						int(decimal-date-time(${today}) - ${dose2_due_days})
select_one yes_no	dose2_given	The second dose was due on ${dose2_due_text}. Has the child received it?	yes
select_one late_reason	late_reason	The second dose is ${days_overdue} days late. What is the main reason?	yes			${dose2_given} = '0' and ${days_overdue} > 0

◀ ▶surveychoicessettings+

list_name	name	label
yes_no	1	Yes
yes_no	0	No
late_reason	1	Did not know the date
late_reason	2	The clinic is too far
late_reason	3	The vaccine was out of stock
late_reason	4	The child was sick
late_reason	-99	Don't know

◀ ▶surveychoicessettings+

days_overdue is negative before the due date, 0 on the day and positive after it, so the reason question only appears for a child who is late and has not had the dose. How relevant works is covered in skip logic in XLSForm.

Common date calculation mistakes and how to fix them

These are the messages pyxform and ODK Validate return, which is what you see when ODK Central or KoboToolbox rejects a form:

You wrote	Write instead	Error you will see
DATEDIF(${admission_date}, ${discharge_date}, 'd')	int(${discharge_date} - ${admission_date})	cannot handle function 'DATEDIF'
EDATE(${loan_date}, 3)	The add-months rows above	cannot handle function 'EDATE'
decimal_date_time(${lmp_date})	decimal-date-time(...) with hyphens	cannot handle function 'decimal_date_time'
(${discharge_date} - ${admission_date}) / 7	(...) div 7	Unexpected beginning of path in expression
date(decimal-date-time(${lmp_date}) + 280	Close the bracket	Mismatched brackets or parentheses in expression
decimal-date-time()	Put the date inside the brackets	Requires 1 arguments but 0 provided
date(decimal-date-time(${lmp_dt}) + 280) with no lmp_dt row	Use the question's exact name	Could not find the name 'lmp_dt'
date(concat(...)) around the parts of a built date	Leave out date(); the YYYY-MM-DD text is already a date	The value "--01" can't be converted to a date
${lmp_date} + 280	date(decimal-date-time(${lmp_date}) + 280)	No error, but the note shows a number such as 21052
int((${today} - ${arrival_date}) div 30) for months	The calendar month formula above	No error, but the count drifts by a month over time

The last two upload without complaint, so preview the form with a few real dates before you deploy. For other messages, see common XLSForm errors, or paste your form into the free XLSForm validator.

Does this work the same in ODK, KoboToolbox and SurveyCTO?

The functions on this page exist on all three. What differs:

Platform	What to know
ODK (XLSForm)	decimal-date-time() and date() are documented as inverses of each other. Everything goes in the calculation column of a calculate row.
KoboToolbox	Same XLSForm syntax. In the Formbuilder, add a Calculate question and type the expression.
SurveyCTO	Its documentation lists decimal-date-time(), date() for turning a string into a date, and format-date-time() for display, so use format-date-time() where this page uses format-date(). Skip logic goes in a column named relevance.

On every platform the calculated date exports as text in YYYY-MM-DD form, which Stata, R and Excel all read as a date. Test the add-months rows on the platform you will collect with before fieldwork, using the last day of a month and 29 February.

Why calculate dates in the form

A due date shown on the screen is one the enumerator can tell the respondent before leaving: "your next visit is on 12 December". A length of stay or a gestational age worked out during the interview can drive skip logic and catch a mistyped year while the respondent is still there. The same numbers computed afterwards in Stata or R arrive weeks too late to correct anything.

Build my first form free

Next: XLSForm calculation examples →