Skip to content
Advanced Excel
All coursesPlacement Report
‹ Back to previous sectionCourse homeAll page URLs

Logical, Text and Date Functions Interview Questions for Advanced Excel

25 Logical, Text and Date Functions questions with answers for advanced excel roles — 11 easy, 11 medium, 3 hard.

About Logical, Text and Date Functions

Formulas that power everyday reports: IF, IFS, AND, OR, SUMIFS, COUNTIFS, text cleaning with TRIM and TEXTBEFORE, date logic with EOMONTH and NETWORKDAYS, plus modern LET and LAMBDA. Expect questions on commission slabs, financial years, error handling and fixing formulas that silently return wrong answers.

Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
3 questions free Unlock the rest in seconds
01

What is the difference between nested IF and IFS? Show an example.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
Nested IF places one IF inside another, which becomes hard to read after three levels. IFS (Excel 2019 and later) tests conditions in order and returns the value for the first true one: =IFS(B2>=90,"A",B2>=75,"B",B2>=60,"C",TRUE,"Fail") The final TRUE acts as the else part. For many bands, a lookup table with XLOOKUP approximate match is easier to maintain than either.
02

HR wants a column that marks employees Eligible for bonus if their rating is 4 or more and tenure is over 1 year, or if they appear in a Top Performers list. How do you write it?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
Combine AND inside OR, and use COUNTIF to check the list: =IF(OR(AND(C2>=4, D2>1), COUNTIF(TopPerformers, A2)>0), "Eligible", "Not Eligible") Here C is rating, D is tenure in years, A is employee ID and TopPerformers is a named range. Matching on ID instead of name avoids errors from spelling differences.
03

What is the difference between SUMIF and SUMIFS?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
SUMIF handles one condition and puts the sum range last; SUMIFS handles many conditions and puts the sum range first: =SUMIF(A:A,"North",C:C) =SUMIFS(C:C,A:A,"North",B:B,"Laptop",D:D,">="&DATE(2026,4,1)) Because of the different order, many people use SUMIFS everywhere, even for a single condition, to stay consistent. COUNTIFS and AVERAGEIFS follow the same pattern.
04

What is the difference between IFERROR and IFNA?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
IFERROR replaces any error, such as #N/A, #DIV/0!, #VALUE! or #REF!, with a value you choose. IFNA replaces only #N/A, which is the normal result when a lookup finds no match. IFNA is safer around lookups because a genuine problem like a deleted column (#REF!) still shows up instead of being hidden as a blank or zero. Use IFERROR only where every error truly means the same thing.
05

Extract the first and last name from a full name like Rahul Kumar Sharma.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
Classic formulas: First: =LEFT(A2,FIND(" ",A2)-1) Last: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)) In Microsoft 365 it is simpler: First: =TEXTBEFORE(A2," ") Last: =TEXTAFTER(A2," ",-1) The -1 tells TEXTAFTER to use the last space, which handles middle names. Run TRIM on the source first to remove double spaces.
06

Explain TRIM, CLEAN, PROPER, UPPER, LOWER and SUBSTITUTE.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
TRIM removes leading, trailing and repeated spaces between words. CLEAN removes non-printable characters such as line breaks from system exports. PROPER, UPPER and LOWER change case, for example PROPER turns RAHUL SHARMA into Rahul Sharma. SUBSTITUTE replaces specific text, like =SUBSTITUTE(A2,"-","") to strip hyphens from a phone number. They are often nested together when cleaning customer master data.
07

Dates in an exported column are left-aligned and formulas like DATEDIF return #VALUE!. How do you fix them?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
Left alignment usually means the dates are stored as text. Select the column, use Data > Text to Columns, choose Date with the correct order such as DMY and finish; Excel converts them to real dates. For mixed or messy text, DATEVALUE or DATE with LEFT, MID and RIGHT works. If the file comes regularly, set the column type in Power Query using Using Locale so day and month are never swapped.
08

What do EOMONTH and EDATE do? Give examples.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
EDATE moves a date by whole months, keeping the day where possible; EOMONTH returns the last day of a month that many months away. =EDATE(A2,3) date three months later, useful for EMI or renewal dates =EOMONTH(A2,0) last day of the same month =EOMONTH(A2,-1)+1 first day of the same month These are standard in billing cycles, month-end closing and ageing reports.
09

Compare CONCAT, TEXTJOIN and the & operator.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
The & operator joins items one by one, CONCAT joins a list or range, and TEXTJOIN adds a delimiter and can skip blanks: =A2&" "&B2 =CONCAT(A2:C2) =TEXTJOIN(", ",TRUE,A2:E2) TEXTJOIN is the best choice for building address lines or a comma-separated list of selected items, because the TRUE argument avoids double commas where cells are empty.
10

How do wildcards work in COUNTIF and SUMIF?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
An asterisk matches any number of characters, a question mark matches exactly one, and a tilde escapes a real * or ?. =COUNTIF(A:A,"*Pvt Ltd*") names containing Pvt Ltd =SUMIFS(C:C,B:B,"INV-2026-????") invoice codes of a fixed length =COUNTIF(A:A,"*~?*") cells that contain a question mark Wildcards work only on text criteria, not on numbers.
11

Explain the common Excel errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NAME? and #SPILL!.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
#N/A means a lookup found no match. #VALUE! means a wrong data type, such as adding text. #REF! means a referenced cell or column was deleted. #DIV/0! is division by zero or a blank. #NAME? means a misspelt function or undefined name. #SPILL! means a dynamic array formula cannot spill because cells in its output area are not empty. Knowing the cause helps you fix the source instead of hiding errors.
12

Count orders from the North region with amount above 50,000 placed in January 2026.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
Use COUNTIFS with one pair of range and criteria per condition, with dates built using DATE so the formula does not depend on regional settings: =COUNTIFS(Orders[Region],"North", Orders[Amount],">50000", Orders[Date],">="&DATE(2026,1,1), Orders[Date],"<"&DATE(2026,2,1)) Using less than 1 February is safer than less than or equal to 31 January when dates contain time values.
13

Why does a date show as a number like 46023 when joined with text, and how do you fix it?

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
Excel stores dates as serial numbers, so joining with & uses the raw number instead of the formatted view. Wrap the date in TEXT with a format code: ="Report date: "&TEXT(A2,"dd-mmm-yyyy") ="Sales: Rs "&TEXT(B2,"#,##0") TEXT returns text, so use it only for labels and titles, never for values that need further calculation.
14

Calculate working days between two dates excluding Sundays and a holiday list.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
NETWORKDAYS excludes Saturday and Sunday by default. For a six-day week common in India, use NETWORKDAYS.INTL with weekend code 11 (Sunday only): =NETWORKDAYS.INTL(A2,B2,11,Holidays[Date]) To find a due date 10 working days after A2: =WORKDAY.INTL(A2,10,11,Holidays[Date]) Keep the holiday list in a Table so it can be updated each year.
15

Calculate an employee's age or tenure in years and months from the joining date.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
DATEDIF is an older hidden function that still works but does not appear in autocomplete: =DATEDIF(B2,TODAY(),"Y")&" years "&DATEDIF(B2,TODAY(),"YM")&" months" Units: Y full years, M full months, D days, YM months after full years, MD days after full months. For a decimal tenure use =YEARFRAC(B2,TODAY()). The start date must be earlier than the end date or DATEDIF returns #NUM!.
16

What does SUMPRODUCT do and when would you use it?

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
SUMPRODUCT multiplies matching items in arrays and adds the results. It is ideal for weighted averages and for conditional sums in older Excel versions: =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10) weighted average price =SUMPRODUCT((A2:A100="North")*(MONTH(D2:D100)=4)*C2:C100) The second example handles a condition on MONTH, which SUMIFS cannot do directly. Avoid full-column references because SUMPRODUCT evaluates every row.
17

Describe a time a formula mistake of yours reached a stakeholder. What did you do?

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
Situation: a regional sales summary I sent showed one zone 18% higher than actual. Task: correct it before decisions were taken. Action: I traced it to a SUMIFS range that stopped one row short, sent a corrected file within the hour with a short note explaining the cause, and added a check cell comparing the summary total to the raw data total. Result: the manager appreciated the quick ownership and the check now catches such gaps automatically.
18

Commission is 2% up to 1 lakh, 5% up to 5 lakh and 8% above 5 lakh, applied as a flat rate on total sales. Write the formula.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
With IFS: =B2*IFS(B2>500000,8%,B2>100000,5%,TRUE,2%) A cleaner and more maintainable option is a slab table (Lower: 0, 100001, 500001; Rate: 2%, 5%, 8%) with approximate match: =B2*XLOOKUP(B2,Slab[Lower],Slab[Rate],,-1) The -1 match mode returns the next smaller value, so changing rates only means editing the table.
19

An IF formula says 100 is not equal to 100. Why might that happen?

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
Usually one value is text and the other is a number, so =A2=B2 returns FALSE even though both show 100. Other causes are hidden spaces, or floating-point results like 99.9999999 that display as 100 because of formatting. Check with ISTEXT, LEN and by widening decimals. Fix with VALUE or TRIM on the text side, or compare with ROUND(A2,2)=ROUND(B2,2) for calculated numbers.
20

Return the Indian financial year (April to March) for a date, like FY 2026-27.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
If the month is April or later, the FY starts in the same year; otherwise it started the previous year: =LET(y,IF(MONTH(A2)>=4,YEAR(A2),YEAR(A2)-1), "FY "&y&"-"&RIGHT(y+1,2)) For 15-May-2026 this gives FY 2026-27, and for 10-Feb-2026 it gives FY 2025-26. The fiscal quarter is =ROUNDUP((MOD(MONTH(A2)-4,12)+1)/3,0).
21

How do you count unique values in a column?

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
In Microsoft 365: =COUNTA(UNIQUE(A2:A500)) =ROWS(UNIQUE(FILTER(A2:A500,A2:A500<>""))) ignores blanks In older versions: =SUMPRODUCT((A2:A500<>"")/COUNTIF(A2:A500,A2:A500&"")) The older formula slows down on large ranges, so for big data use a pivot table with Distinct Count via the Data Model.
22

Tell me about a time you had to explain a complex Excel report to a non-technical manager.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
Situation: my manager was unsure why the new incentive sheet showed lower payouts. Task: explain the logic without formulas. Action: I added a one-page summary with the slab table, walked through two real employees step by step, and used colour to show which inputs drove the result. Result: the manager understood it in one meeting, approved the sheet and asked me to add the same summary to future models.
23

What is the LET function and why is it useful?

Logical, Text and Date FunctionsHard
Save to ▾
Show answer
LET assigns names to intermediate results inside one formula, so a value is calculated once and the formula reads like steps: =LET(sales,SUMIFS(Data[Amt],Data[Rep],A2), target,XLOOKUP(A2,Targets[Rep],Targets[Target]), IF(target=0,"",sales/target)) Without LET, the SUMIFS would be repeated in each branch. It improves speed on large sheets and makes complex formulas easier to review.
24

What is LAMBDA and how do you create a custom function without VBA?

Logical, Text and Date FunctionsHard
Save to ▾
Show answer
LAMBDA lets you define a reusable function from a formula. Create it in Name Manager, for example name GSTInclusive with: =LAMBDA(amount,rate,ROUND(amount*(1+rate),2)) Then use =GSTInclusive(B2,18%) anywhere in the workbook. Helper functions like MAP, BYROW, REDUCE and SCAN apply a LAMBDA across arrays. Unlike VBA functions, LAMBDAs need no macro-enabled file, but they require Microsoft 365.
25

Your monthly report formulas break every time a column is added to the export. How do you make them robust?

Logical, Text and Date FunctionsHard
Save to ▾
Show answer
Avoid hard-coded column numbers and fixed ranges. Load the export into a Table or through Power Query, so columns are referenced by name. Replace VLOOKUP with column index 7 by XLOOKUP or INDEX/MATCH, keep constants like rates in labelled assumption cells, and use LET for readability. Add a check cell that compares the report total to the raw total, so any break is caught immediately.

Frequently asked questions

How important is Logical, Text and Date Functions in Advanced Excel interviews?

Formulas that power everyday reports: IF, IFS, AND, OR, SUMIFS, COUNTIFS, text cleaning with TRIM and TEXTBEFORE, date logic with EOMONTH and NETWORKDAYS, plus modern LET and LAMBDA. Expect questions on commission slabs, financial years, error handling and fixing formulas that silently return wrong answers.

How should I use the Logical, Text and Date Functions sheet?

Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.

More question sheets

Excel Fundamentals and Formatting questionsLookup and Dynamic Array Functions questionsPivot Tables and Data Summaries questionsCharts and Dashboards questionsPower Query and Data Cleaning questionsPower Pivot, Data Model and DAX questionsMacros and VBA Automation questions