Text, Date and Time Functions

Introduction

Business spreadsheets rarely contain perfectly structured information. Customer names may be stored in separate columns, dates may be imported as text, identifiers may require formatting, and operational reports often depend on elapsed time. Excel functions provide a controlled way to transform such information into usable management data.

This chapter develops selected logical, lookup, text, date and time functions used in business analysis. Particular attention is given to IF, AND, OR, VLOOKUP, HLOOKUP, INDEX, MATCH, TEXT, DATE, CONCATENATE and TEXTJOIN. The emphasis is not merely on syntax, but on design choices, auditability, error prevention and managerial interpretation.

Learning Objectives

By the end of this chapter, the reader should be able to:

• distinguish Excel values from their displayed formats;
• construct and combine text values reliably;
• create, compare and analyse dates and times;
• use logical functions to classify business conditions;
• select appropriate lookup methods for business reporting;
• build formulas for aging, schedules, reporting periods and performance measurement; and
• identify limitations and governance risks in spreadsheet-based information processing.

1. Excel Values, Types and Formula Design

Excel stores dates and times as serial numbers. A date is represented by an integer counting days from Excel's date system, while a time is represented by the fractional part of a day. For example, noon is approximately 0.5 because it is half of a 24-hour day. Formatting changes how a value appears but does not normally change its underlying value.

This distinction is fundamental. A cell containing the numeric date value 45292 can be displayed as 01-Jan-2024, 1/1/24 or January 2024. Similarly, a value such as 0.75 can be displayed as 18:00. Calculations require genuine numeric dates and times; text that merely resembles a date may not calculate correctly.

A robust formula should have a clear purpose, consistent references and explicit treatment of missing or invalid data. Absolute references, such as $B$2, are useful when a threshold or lookup table must remain fixed while a formula is copied.

2. Logical Functions for Business Classification

Logical functions convert conditions into decisions. Their results are normally TRUE or FALSE, or are used inside IF to return a business label or value.

IF

The basic structure is:

For example, if invoice age is in cell E2:

=IF(E2>30,"Overdue","Current")

The formula classifies an invoice as overdue when its age exceeds 30 days. Text outputs must be enclosed in quotation marks. Nested IF functions can represent multiple categories, but excessive nesting is difficult to audit. Where available, IFS or a lookup table may provide a clearer design.

AND and OR

AND returns TRUE only when every supplied condition is true. OR returns TRUE when at least one condition is true.

=IF(AND(C2="Approved",D2>=10000),"Escalate","Routine")

This identifies approved transactions of at least 10,000 for escalation. By contrast:

=IF(OR(B2="High",C2="Critical"),"Immediate action","Monitor")

classifies a case when either condition is present.

These functions are valuable for credit decisions, exception reporting, service-level monitoring and compliance checks. However, a formula should not disguise policy. Thresholds, definitions and exceptions should be documented, preferably in a controlled assumptions area.

3. Lookup Functions

Lookup functions connect a transaction to a reference table. Typical uses include retrieving a product price, department name, tax rate, risk category or target value.

VLOOKUP

VLOOKUP searches vertically in the first column of a table and returns a value from a specified column.

=VLOOKUP(A2,$H$2:$K$100,3,FALSE)

Here, Excel searches for A2 in H2:H100 and returns the corresponding value from the third column of H2:K100. FALSE requests an exact match; this is generally appropriate for identifiers. TRUE requests an approximate match and requires the first column to be sorted in ascending order. Approximate matching is useful for bands such as commission rates or credit scores, but an unsorted table can produce incorrect results.

Limitations include the requirement that the search column be the table's first column and the fragility of a hard-coded column number when columns are inserted.

HLOOKUP

HLOOKUP performs the analogous operation horizontally. It searches the first row of a range and returns a value from a specified row.

=HLOOKUP(B1,$B$5:$M$8,4,FALSE)

This may be suitable for a report where months occupy the top row and metrics occupy subsequent rows. Nevertheless, vertical tables are usually easier to extend and audit.

INDEX and MATCH

INDEX returns a value at a specified row and column position. MATCH returns the position of a searched value within a range.

=INDEX($J$2:$J$100,MATCH(A2,$H$2:$H$100,0))

MATCH(...,0) requests an exact match. The formula searches for A2 in H2:H100, obtains its position, and uses that position to return the associated value from J2:J100. This approach separates the search range from the return range and can look to the left or right, making it more flexible than VLOOKUP.

A lookup design should specify how duplicates are handled. If an identifier is not unique, the result may represent only the first match, not the complete business reality. Data validation and unique keys are therefore essential.

4. Text Functions and Information Presentation

Text functions are used to clean, label and combine business information. They do not necessarily change the underlying business facts; they create a usable representation of those facts.

TEXT

TEXT converts a numeric value into text using a specified format.

=TEXT(B2,"mmm yyyy")

If B2 contains a valid date, the result may be displayed as “Jan 2024”. Other examples include:

=TEXT(C2,"#,##0.00")
=TEXT(D2,"0.0%")
=TEXT(E2,"dd-mmm-yyyy")

The result is text, not a number. Consequently, a value converted with TEXT should not be used for later arithmetic without converting it back or retaining the original numeric value. A strong reporting practice is to preserve the original value in one column and create a formatted display column separately.

CONCATENATE and the ampersand operator

CONCATENATE joins text values:

=CONCATENATE(A2," - ",B2)

If A2 is “East” and B2 is “Region”, the result is “East - Region”. The equivalent modern expression is:

=A2&" - "&B2

The ampersand is concise, while CONCATENATE makes the joining operation explicit. In newer Excel versions, CONCATENATE is retained for compatibility, but TEXTJOIN is generally better for lists.

TEXTJOIN

TEXTJOIN combines multiple text values using a delimiter and can ignore empty cells.

=TEXTJOIN(", ",TRUE,A2:D2)

The first argument is the delimiter, the second specifies whether empty cells should be ignored, and the third identifies the range. If the cells contain “Finance”, blank, “Audit” and “Risk”, the result is “Finance, Audit, Risk”. This is useful for project teams, customer attributes, exception reasons and multi-department responsibility lists.

Text joining is not the same as data normalization. A comma-separated list is convenient for presentation but less suitable for filtering, relational analysis or joining to another system. Where each item has independent meaning, separate rows or a structured table are preferable.

5. Creating and Analysing Dates and Times

DATE

DATE creates a valid Excel date from separate year, month and day components:

=DATE(A2,B2,C2)

If A2 is 2025, B2 is 4 and C2 is 15, the result is 15-Apr-2025 when formatted as a date. Using DATE is safer than concatenating date components as text because it produces a numeric date suitable for sorting and calculation.

Excel may normalize out-of-range components. For example, a month value of 13 can roll into the next year. This behaviour can be useful for date arithmetic but can also conceal data-entry errors; input validation remains necessary.

Date arithmetic and reporting periods

Because dates are serial numbers, subtracting two dates gives elapsed days:

=EndDate-StartDate

For example, if a contract starts on 1 March and ends on 31 March in the same year, the difference is 30 days. Whether the business calls this 30 or 31 days depends on the inclusion rule. A schedule must therefore state whether start and end dates are inclusive.

Common reporting formulas include:

=DATE(YEAR(A2),MONTH(A2),1)
=EOMONTH(A2,0)
=YEAR(A2)
=MONTH(A2)

The first returns the first day of the month containing A2; EOMONTH returns the month-end date; YEAR and MONTH extract components. These formulas support monthly reporting without relying on text labels.

Aging analysis

An aging measure often uses the reporting date minus the transaction date:

=MAX(0,$B$1-A2)

If B1 is the reporting date and A2 is the invoice date, the result is elapsed days, with future-dated invoices assigned zero rather than a negative age. A simple aging category is:

=IF(C2<=30,"0–30",IF(C2<=60,"31–60",IF(C2<=90,"61–90","90+")))

The boundary convention must be documented. Here, an age of 30 belongs to 0–30, while 31 belongs to 31–60.

Time and performance measurement

A time is a fraction of a day. Thus, elapsed hours can be calculated as:

=(EndTime-StartTime)*24

If a shift crosses midnight, a robust expression is:

=MOD(EndTime-StartTime,1)*24

MOD ensures a positive within-day duration. For elapsed minutes, multiply the time difference by 1,440, the number of minutes in a day. These formulas assume valid time values and do not automatically account for breaks, holidays, daylight-saving transitions or staff working across time zones.

For a service-level measure, a case may be compliant when elapsed hours are no greater than a target:

=IF(ElapsedHours<=TargetHours,"Met","Missed")

The result is a managerial classification, not proof that the underlying timestamps are accurate.

6. Integrated Worked Example: Customer Service Operations

Consider an illustrative customer-service workbook with the following columns:

A product reference table in J:L contains product code, product name and service target hours.

Step 1: Create a customer label

=TEXTJOIN(" ",TRUE,B2:C2)

This produces a full name while ignoring a missing name component.

Step 2: Retrieve the product name

=INDEX($K$2:$K$200,MATCH(G2,$J$2:$J$200,0))

The exact-match argument reduces the risk of assigning a nearby but incorrect product.

Step 3: Retrieve the target and measure performance

=VLOOKUP(G2,$J$2:$L$200,3,FALSE)
=IF(AND(H2<=I2,F2<>"Low"),"Priority SLA met","Review")

The first formula retrieves the target hours. The second identifies cases meeting the target when priority is not Low. The policy condition should be confirmed with management: excluding Low priority from the classification may be appropriate, or it may unintentionally hide poor performance.

Step 4: Calculate aging for open cases

If the reporting date is stored in B1:

=IF(E2="",MAX(0,$B$1-D2),E2-D2)

This calculates age to the reporting date for open cases and duration to closure for completed cases. It assumes a blank close date means “still open”; if blanks can also mean “data missing”, a separate status field is required.

Step 5: Create a reporting-period label

=TEXT(D2,"yyyy-mm")

This is convenient for display and grouping. For robust date filtering, retain the actual date and use the first-of-month date created by DATE as the grouping key.

7. Case Application: Monthly Accounts-Receivable Review

This is an illustrative case. A regional distributor receives an export containing invoice numbers, invoice dates, customer codes and amounts. Management wants a monthly dashboard showing customer names, invoice age, aging bands and collection-risk flags.

Problem

The export contains customer codes but not names, dates in mixed formats, and blank due dates. A manually prepared report has produced inconsistent aging categories and occasional incorrect customer assignments.

Analysis

The finance manager establishes three controls: customer codes must be unique in the master table; the reporting date must be a single controlled input; and the aging policy must define boundaries. The policy defines age as reporting date minus invoice date, with categories 0–30, 31–60, 61–90 and 90+ days.

Approach and solution

The customer name is retrieved with INDEX and MATCH using exact matching. Imported year, month and day fields are converted with DATE. Invoice age is calculated using a controlled reporting date. IF and nested conditions assign the aging band, while AND flags invoices that are both over 60 days and above the materiality threshold. TEXTJOIN creates a readable list of collection actions, such as “Call, Escalate, Dispute review”, from separate action columns.

Outcome

The dashboard becomes repeatable: changing the reporting date updates age and classifications without rewriting the report. Management can distinguish data-quality exceptions from genuine overdue balances. The approach also improves auditability because the source date, lookup key, threshold and policy assumptions remain visible.

Learning points

Automation does not eliminate judgment. A wrong customer code, duplicate master record or misunderstood inclusion rule can generate a technically correct but economically misleading report. Formula design must therefore be combined with data validation, reconciliation and review of exceptions.

8. Limitations, Governance and Best Practice

First, date systems can differ. Excel's default Windows date system and the Mac 1904 date system have different serial origins. Files exchanged across systems should be checked for date shifts. Second, imported values may be text rather than dates. A displayed date is not evidence of a valid date value.

Third, formulas can create false confidence. A lookup returning a result does not prove that the key is correct, unique or current. Reference tables require ownership, effective dates and version control. Fourth, formulas that use NOW or TODAY change over time and may make historical reports irreproducible unless the reporting date is copied as a controlled value.

Good practice includes:

• separating raw data, calculations, reference tables and presentation;
• using exact matches for identifiers unless approximate logic is explicitly required;
• documenting thresholds, date boundaries and blank-cell meanings;
• testing formulas with boundary cases, such as 30, 31 and 90 days;
• preserving numeric dates and amounts alongside formatted text;
• checking duplicate and missing lookup keys;
• protecting formula cells and controlling input cells; and
• reconciling totals to the source system before decisions are made.

From an ethical perspective, automated classifications may affect customers, employees or suppliers. Managers should be able to explain the rule, correct inaccurate source data and identify who approved the thresholds. Sensitive personal information should be minimized, access-controlled and retained only as necessary.

Chapter Summary

Excel text, date, time, logical and lookup functions form a practical toolkit for transforming operational data into management information. IF, AND and OR encode decision rules. VLOOKUP, HLOOKUP, INDEX and MATCH connect transactions to controlled reference data. TEXT, CONCATENATE and TEXTJOIN create readable labels and lists, while DATE and Excel's serial-date system support schedules, aging analysis, reporting periods and performance measures. Reliable spreadsheet analysis depends on more than correct syntax: it requires valid data types, unique keys, explicit assumptions, boundary testing, governance and managerial review.

Key Terms

Excel serial date: Numeric representation of a date or time.

Logical test: A condition evaluated as TRUE or FALSE.

IF: Function that returns one result when a condition is true and another when it is false.

AND: Function that is TRUE only when all conditions are TRUE.

OR: Function that is TRUE when at least one condition is TRUE.

Lookup: Process of retrieving a related value from a reference table.

VLOOKUP: Vertical lookup based on the first column of a table.

HLOOKUP: Horizontal lookup based on the first row of a range.

INDEX: Function that returns a value at a specified position.

MATCH: Function that returns the position of a searched value.

Exact match: Lookup requiring equality between the search key and reference key.

Approximate match: Lookup that selects a value within an ordered range or band.

TEXT: Function that converts a value to formatted text.

CONCATENATE: Function that joins text values.

TEXTJOIN: Function that joins text using a delimiter and optional blank-cell handling.

DATE: Function that constructs a date from year, month and day components.

Aging analysis: Measurement of elapsed time since an event, commonly an invoice date.

Reporting period: Defined interval used to group and compare business activity.

Service-level measurement: Comparison of actual performance with an agreed target.

Review Questions

Short Answer Questions

1. Why can a value displayed as a date still be unsuitable for date arithmetic?
2. What is the difference between exact and approximate lookup?
3. When is TEXTJOIN preferable to CONCATENATE?
4. What does AND return when one supplied condition is FALSE?
5. Why should a reporting date be controlled rather than repeatedly typed into formulas?

Long Answer Questions

1. Explain how INDEX and MATCH work together and compare their advantages with VLOOKUP.
2. Discuss the design and governance requirements of an accounts-receivable aging model.
3. Explain how Excel date serial numbers support schedules and performance measurement, including important limitations.
4. Compare TEXT, CONCATENATE and TEXTJOIN in a management-reporting context.
5. Evaluate the risks of using nested logical formulas to implement organizational policy.

Analytical / Application Questions

1. Design formulas for a dashboard that identifies cases older than 60 days, retrieves the responsible manager from a reference table and produces a readable action list.
2. A lookup table contains duplicate customer codes. Analyse the consequences for a financial report and propose controls to prevent misleading results.
3. Develop a date and time approach for measuring service response when shifts cross midnight and scheduled breaks must be excluded. State the assumptions that must be confirmed.

Multiple Choice Questions with Answers

Question 1

Which function returns TRUE only when every supplied condition is TRUE?

A. OR

B. AND

C. IF

D. MATCH

Correct Answer: B. AND

Question 2

In =VLOOKUP(A2,$H$2:$K$100,3,FALSE), what does FALSE request?

A. An approximate match

B. A reverse lookup

C. An exact match

D. A blank result

Correct Answer: C. An exact match

Question 3

Which formula creates a valid date from year, month and day components?

A. TEXT

B. DATE

C. CONCATENATE

D. HLOOKUP

Correct Answer: B. DATE

Question 4

What is the principal result of applying TEXT to a numeric date?

A. A formatted text value

B. A lookup table

C. A logical value

D. A deleted date

Correct Answer: A. A formatted text value

Question 5

Which function is most suitable for joining several cells with a delimiter while ignoring blanks?

A. TEXTJOIN

B. DATE

C. MATCH

D. AND

Correct Answer: A. TEXTJOIN

Question 6

In INDEX/MATCH, what does MATCH(...,0) specify?

A. Descending order

B. Approximate matching

C. Exact matching

D. The return column

Correct Answer: C. Exact matching

Question 7

If a valid Excel time difference is multiplied by 24, the result is expressed in:

A. Minutes

B. Hours

C. Days squared

D. Months

Correct Answer: B. Hours

Question 8

Which formula is most appropriate for an invoice age that cannot be negative?

A. =MAX(0,ReportingDate-InvoiceDate)

B. =MIN(0,ReportingDate-InvoiceDate)

C. =TEXT(ReportingDate-InvoiceDate,"days")

D. =DATE(ReportingDate,InvoiceDate,0)

Correct Answer: A. =MAX(0,ReportingDate-InvoiceDate)

Question 9

A major limitation of VLOOKUP is that:

A. It cannot return text

B. Its search column must be the first column of the table

C. It cannot use exact matching

D. It works only with dates

Correct Answer: B. Its search column must be the first column of the table

Question 10

If a list contains “Finance”, a blank cell, and “Audit”, which formula returns “Finance, Audit”?

A. =TEXTJOIN(", ",TRUE,A1:C1)

B. =DATE(A1,C1,TRUE)

C. =AND(A1:C1)

D. =HLOOKUP(A1,C1,2,FALSE)

Correct Answer: A. =TEXTJOIN(", ",TRUE,A1:C1)