To use a random date generator in Excel for a date between January 1 and December 31, 2026, enter =RANDBETWEEN(DATE(2026,1,1),DATE(2026,12,31)) in a cell and apply a date format. Both endpoints can be selected. Use date cells instead of typed date text when the range needs to be adjustable, and paste results as values when you need a fixed sample.
01 / FORMULA
Use RANDBETWEEN with DATE for clear boundaries
Excel dates are numeric values displayed through a date format. That lets a random integer function select a calendar day when its lower and upper bounds are valid dates. For a date in 2026, use =RANDBETWEEN(DATE(2026,1,1),DATE(2026,12,31)). DATE builds each boundary from a year, month, and day, which avoids ambiguous text such as 1/2/2026 when a workbook is opened under another regional setting.
For a range that you may edit later, put the first date in B2 and the last date in C2, then use =RANDBETWEEN(B2,C2). Format B2 and C2 as dates before entering them. The start must be on or before the end. If both cells contain the same date, that one date is the only valid result; if the bounds are reversed, fix the inputs before expecting a useful sample.
| Task | Formula | What it does |
|---|---|---|
| One date in 2026 | =RANDBETWEEN(DATE(2026,1,1),DATE(2026,12,31)) | Chooses one calendar day, including Jan 1 and Dec 31. |
| Use worksheet bounds | =RANDBETWEEN(B2,C2) | Uses the date values stored in B2 and C2. |
| Fill a short list | Copy =RANDBETWEEN(B2,C2) down | Calculates a separate draw in each row; repeats can occur. |
02 / DISPLAY
Turn a date serial into a readable calendar value
A random date generator in Excel returns a numeric date serial, so a formula may initially display a five-digit number. That is usually the underlying value, not a failed random draw. Select the result cells, open Format Cells, choose a date style, or apply a custom format such as yyyy-mm-dd. The displayed date changes while the stored numeric value remains suitable for sorting, subtracting, filtering, and comparison.
Use a long date style when a person will read the result. Use yyyy-mm-dd when the value will be copied into a database, CSV file, or test fixture because the order is unambiguous. Avoid converting the result with TEXT unless you deliberately need text: a text string looks like a date but does not behave like a numeric date in later calculations.
If imported dates are text, convert them before applying a date format. Excel supports 1900 and 1904 date systems; when copying values between workbooks, check that both use the same system.
03 / BATCHES
Fill rows or use a supported dynamic array
For a small sample, enter the RANDBETWEEN formula in the first output cell and copy it down. Each row recalculates independently, so the list can contain the same date more than once. Keep the start and end cells fixed with absolute references such as $B$2 and $C$2 if you plan to drag the formula across columns or into a larger table.
Excel editions that support RANDARRAY can return a spill range from one formula. For ten dates between the same bounds, try =RANDARRAY(10,1,B2,C2,TRUE), then format the output range as dates. If Excel reports #NAME?, use the copied RANDBETWEEN formula instead; available functions depend on the installed Excel edition and update channel.
A batch can repeat dates. Ten independent draws are not guaranteed unique, and a request for more unique values than eligible days cannot be satisfied unless you change the range or rule.
- Use absolute references when copying a formula tied to fixed bounds.
- Format the full output range, including cells filled by RANDARRAY.
- Check the eligible-day count before requesting a unique batch.
04 / CALENDAR RULES
A random weekday is not automatically a business day
The basic RANDBETWEEN formula includes Saturdays, Sundays, and public holidays because it samples every calendar date in the range. If your task means a working day, define the calendar first. Excel's WORKDAY and NETWORKDAYS functions can apply weekend rules and an explicit holiday list, but a random date formula does not discover the correct holiday calendar for your country or organization.
For a reliable business-day sample, make an eligible-date list from the approved range and holiday source, confirm that it contains at least one date, then choose from that list. This also makes the selection rule reviewable. Avoid generating any date and shifting a weekend result forward afterward: that can bias the distribution and may move the date beyond the requested end.
Holiday lists need a clear source and date range. For a real booking or deadline, verify the selected date against the responsible calendar instead of treating a random formula as availability data.
05 / EDGE CASES
Treat uniqueness and edge dates as separate requirements
RANDBETWEEN does not prevent duplicate results. If every row must be unique, first create a finite list of eligible dates, assign each candidate a random helper value, sort by that value, and take the required number of rows. Check that the candidate list contains at least as many dates as the requested output. For a small list, another safe option is to generate a larger sample and review duplicates, but that does not guarantee success.
Random sampling also does not guarantee that a rare boundary appears. Add deliberate cases for the first and last date, February 29 in a leap year, the first day of a month, the last day of a month, and a year change when those rules matter. Keep these fixed cases separate from the random sample so a test does not depend on luck.
Use a single-day range for one fixed date, and add deliberate month-end or leap-day cases when they matter. Keep explicit cases separate so coverage does not depend on chance.
- Use a candidate list when uniqueness is mandatory.
- Add leap days and month ends as deliberate cases.
- Do not treat a random sample as proof that every edge works.
06 / TROUBLESHOOTING
Diagnose formula, locale, and recalculation issues
If the result is a serial number, change the number format. If a random date generator in Excel shows #NAME?, your Excel build may not support a function such as RANDARRAY, or the function name may be localized. If you see #NUM!, check that the lower bound is not after the upper bound and that a filtered eligible list is not empty.
Examples use English function names and commas. Local Excel settings may translate names or use semicolons; adjust the syntax but keep bounds in DATE expressions or real date cells.
RANDBETWEEN and RANDARRAY recalculate when the sheet changes or F9 is pressed. To lock a sample, copy the results and use Paste Special, Values, then confirm the formulas were replaced by fixed values.
A REPEATABLE WORKFLOW
From a date range to a stable sample
- Choose the rangeEnter real start and end dates and verify that the first is not after the last.
- Enter the formulaUse DATE boundaries or worksheet date cells inside RANDBETWEEN.
- Format the outputChoose a human-readable date or yyyy-mm-dd for data exchange.
- Check the ruleReview weekends, holidays, duplicates, and any required boundary cases.
- Freeze the sampleCopy the final results and paste values when they must stay stable.
COMMON QUESTIONS
Random date generator in Excel FAQ
What formula creates a random date in Excel?
Use =RANDBETWEEN(DATE(2026,1,1),DATE(2026,12,31)) for one date in 2026. Replace the DATE expressions with real start and end date cells when the range should be editable.
Does RANDBETWEEN include both dates?
Yes. RANDBETWEEN can return either its bottom or top integer. When those values are the serial numbers for two dates, the first and last dates are both eligible. A reversed range is invalid and should be corrected before generating results.
Why does Excel show a number instead of a date?
Excel stores dates as serial values and uses the cell's number format to display them. Select the result and choose a date format such as yyyy-mm-dd. If formatting has no effect, the source may be text rather than a real date value.
How can I stop random dates from changing?
Copy the generated result cells and use Paste Special, Values. This replaces volatile formulas with their current numeric date values. Keep the cells formatted as dates and save a separate copy of the workbook if you also need to preserve the original formulas.
Can Excel generate a list of random dates?
Yes. Copy a RANDBETWEEN formula down for a simple list, or use RANDARRAY in an Excel edition that supports it. Every result recalculates, and duplicates are possible unless you explicitly select from a unique candidate list.
Can a random date formula exclude weekends and holidays?
The basic formula does not. Define an eligible list and use an explicit holiday source when business-day rules matter. WORKDAY and NETWORKDAYS can help build or check that list, but they do not supply a complete calendar for every country or organization.
Does RANDBETWEEN guarantee unique dates?
No. Each cell draws independently, so repeats are possible. For unique output, build an eligible date list, confirm it is large enough, and select without replacement or sort candidates by random helper values.
Official references
Microsoft's documentation describes the function bounds and the workbook date systems behind these examples. Formula names and argument separators can vary with Excel language settings.
- Microsoft Support: RANDBETWEEN functionFunction arguments, inclusive integer bounds, and recalculation behavior.
- Microsoft Support: Date systems in ExcelHow Excel stores calendar dates as serial values and supports workbook date systems.
NEED A QUICK DATE PICK?
Generate a date without building a workbook
Set an inclusive range in the browser tool, choose a format, and copy a result. Use Excel when you need formulas, workbook logic, or a repeatable data sheet.