158 useful answers · Page 5 of 7
Use formulas, clean imported data, combine tables, and understand calculation errors.
Search this topicUse =DATEDIF(A2,B2,"Y") with the earlier date first. It counts completed years rather than dividing the number of days by 365. This is useful for anniversaries or elapsed whole-year periods. Check the date values and decide what should happen when an end date is missing or earlier than the start.
Use =DAYS(B2,A2), with the end date first and the start date second. It returns the elapsed day difference, so consecutive dates are one day apart. If your business rule counts both endpoints as included days, that is a separate adjustment. Do not add one without first deciding which counting convention you need.
Use =TODAY() and format the cell as a date. It updates when the spreadsheet recalculates, so it is suitable for a current-date reference. It is not a permanent record of when a row was entered. If you need a fixed event date, enter or store a value instead of relying on TODAY.
Use =NOW() and choose a date-and-time display format. The value reflects the most recent recalculation; it is not a clock guaranteed to update every second. Check the spreadsheet's time-zone settings if the displayed time surprises you. Do not use this formula as an unchanging timestamp for a past action.
Use =TIME(A2,B2,C2) with numeric components, then format the result as a time. For 14, 30, and 0, it represents 2:30 PM. TIME normalizes out-of-range components, so validate inputs if impossible times should be rejected. It represents time within a day, not an unrestricted duration of many days.
Use =TIMEVALUE(A2) for recognizable time text such as 14:30, then apply a time format. The underlying result is a fraction of a day, not a count of minutes. Multiply by 24 or 1,440 only when you explicitly need hours or minutes. Verify imported text before using it in elapsed-time calculations.
Use =HOUR(A2) to return the hour component from zero through 23. It is useful for grouping events by hour of day. It does not return total elapsed hours from a duration longer than one day. Keep the original timestamp if you also need to distinguish the date or calculate a complete time interval.
Use =MINUTE(A2) for a valid time or timestamp. A time of 14:35 returns 35, not the total minutes since midnight. To convert a time-of-day value into total minutes, the calculation is different. Check whether you need a component or a duration before choosing the formula, especially for time-tracking data.
Use =SECOND(A2) to return the seconds component of a valid time value. For 10:15:42, it returns 42. This is not the total elapsed seconds represented by a duration. Retain the full original value for interval calculations, and do not mistake the displayed precision for proof that an imported timestamp contains no finer detail.
Use =EDATE(A2,3) and format the result as a date. A negative month count moves backward. Calendar-month calculations differ from adding a fixed 90 days because months vary in length. Check end-of-month examples in your data and decide whether you need the corresponding date or specifically the last day of the target month.
Use =EOMONTH(A2,0) for the last day of A2's month, or change zero to one for the following month's end. Format the returned value as a date. This avoids guessing whether a month has 28, 29, 30, or 31 days. Use valid date inputs rather than ambiguous text where possible.
Use =WEEKDAY(A2,2). The second argument makes Monday 1 and Sunday 7. Without that explicit setting, the numbering convention differs. This is useful for scheduling checks, but the returned number does not account for holidays. Keep the chosen convention documented if other formulas or users depend on the result.
Use WEEKNUM with an explicit numbering type, such as =WEEKNUM(A2,2) for a Monday-start system whose first week contains January 1. That is not the same as ISO week numbering. Confirm the convention used by your report or organization before grouping dates; different valid systems can assign different numbers near year boundaries.
Use =ISOWEEKNUM(A2). ISO weeks start on Monday, and week one is the week containing the year's first Thursday. Dates near January 1 can therefore belong to the previous or following ISO week-year. If you build an annual weekly report, do not pair the week number with calendar YEAR without checking that boundary.
Use =NETWORKDAYS(A2,B2,H2:H10), where H2:H10 contains holiday dates to exclude. It normally treats Saturday and Sunday as weekends and counts qualifying endpoints. Use real date values and check the holiday list. For a different weekend pattern, use NETWORKDAYS.INTL rather than manually subtracting an assumed number of weekends.
Use =NETWORKDAYS.INTL(A2,B2,"0000001",H2:H10). The seven-character pattern runs Monday through Sunday: zero means a workday and one means a weekend day. The optional holiday range excludes additional dates. Verify the pattern against your actual schedule; this formula will count Saturdays as workdays, unlike the standard NETWORKDAYS function.
Use =WORKDAY.INTL(A2,5,1,H2:H10). It advances five working days from A2, using the standard Saturday–Sunday weekend and excluding the listed holidays. Format the result as a date. This counts working days after the start rather than treating the starting date as day one; check that this matches your deadline rule.
Use =ROUND(A2,2) to change the calculated value to two decimal places. This differs from merely displaying fewer decimals, which can leave hidden precision in later calculations. Decide where rounding belongs in your workflow before applying it to every intermediate step. Multiple stages of rounding can produce a different final total.
For a positive item count in A2 and box capacity in B2, use =ROUNDUP(A2/B2,0). Twenty-three items with capacity six require four boxes. Validate that capacity is greater than zero and quantities are sensible. Rounding up is appropriate because a partly filled final box still needs to be counted.
For nonnegative quantities, use =ROUNDDOWN(A2/B2,0), where A2 is the item count and B2 is group size. Twenty-three items in groups of six give three complete groups. Check that group size is positive. This intentionally excludes leftovers; use MOD separately when you also want to show the remaining items.
Use =MROUND(A2,5) for positive values. It chooses the nearest multiple, so 23 becomes 25 while 22 becomes 20. The value and factor must have compatible signs. This differs from always rounding upward or downward, so choose CEILING or FLOOR if your rule requires a particular direction.
Use =CEILING(A2,5). For example, 21 becomes 25, while an exact multiple such as 20 remains 20. This is useful when units must be allocated in fixed increments. Validate your input and factor, especially if negative values are possible, because directional rounding needs an explicit rule for those cases.
Use =FLOOR(A2,5). A value of 24 becomes 20, and an exact multiple stays unchanged. This deliberately discards the remainder below the next increment. Make sure that downward rounding is appropriate for your task; it would understate a requirement where you must allocate enough capacity to cover every item.
INT rounds toward negative infinity, so =INT(-2.7) returns -3. ROUNDDOWN toward zero gives -2 when used with zero decimal places. Choose according to the mathematical rule you need, rather than treating both as interchangeable ways to remove decimals. The difference may be hidden when all your test values are positive.
Source checks for these answers: September 21, 2026. Product instructions and local requirements can vary.