Time, dates and text

---

When you type something into a cell, Excel checks whether it can be processed as a number. If it can’t like when entering a name it’s treated as text and is automatically aligned to the left of the cell.

If you input a date, Excel recognizes it and aligns it to the right. It also formats the cell as a date automatically. Excel supports various date styles, from short (e.g., 1/1/2013) to long (e.g., 1 January 2013). You can adjust how dates appear using the Format Cells menu (Ctrl + 1).

Behind the scenes, Excel converts dates into numbers based on how many days have passed since January 1, 1900. For instance, January 1, 2013, is stored as 41275, since that's how many days lie between it and the base date. This numerical system allows date arithmetic, even though users typically see formatted dates.

When entering time, Excel stores it using a Time or Custom format. Times are saved as fractions of a 24-hour period. For example, 6:00 AM is 0.25, and 3:00 PM (15:00) becomes 0.625.

Text data often comes in inconsistent capitalization. You can fix this using:

• LOWER() – changes text to all lowercase.
• UPPER() – converts text to uppercase.

Both take a single text input.

Use PROPER() to make only the first letter of each word uppercase and all other letters lowercase. For example, “john DOE” becomes “John Doe”.

To create things like usernames from parts of names, combine them using the & symbol or functions like CONCAT() or TEXTJOIN().

• & joins parts directly (e.g., A1 & "_" & B1).
• CONCAT() works like &, but uses commas between arguments.
• TEXTJOIN() lets you specify a separator and skip empty cells, which is handy for complex merges.
   

• LEFT() grabs characters from the beginning of a string.
• RIGHT() pulls characters from the end.
• MID() extracts from the middle, starting at a specific position for a set length.

To get a domain like "gmail" from "user@gmail.com", you’ll combine MID() with FIND().

To isolate the provider:

1. Use FIND("@", email) to locate the "@" symbol.
2. Add 1 to get the start of the provider.
3. Use another FIND() to locate the period after the provider.
4. Subtract start from end to get length.
5. Feed that info into MID() to extract just the provider.
    

To locate text manually, press Ctrl + F and enter your search. If you want to search within a specific column, select that column first.

To detect whether a cell includes "CA" (California), FIND("CA", cell) returns a position or an error. Wrap it with ISNUMBER() to get TRUE or FALSE, or use IF() for custom logic.

If you want to break addresses into parts like city or state, use the Text to Columns tool (Alt + A + E). In the wizard:

1. Choose “Delimited.”
2. Pick your delimiter (usually a comma).
3. Assign formats for the new columns

Now you can filter or analyze each part separately.

Use TRIM() to remove extra spaces from text. This is especially useful after splitting text, as new columns often contain leading or trailing spaces.

Split the column with state and postal code using space as the delimiter in Text to Columns. Make sure postal codes are formatted as text to avoid number formatting issues.

When a date is split into three columns, combine them by joining the values into a single text string, then convert that string into a date with DATEVALUE(). Once the date column is created, delete the originals to simplify your sheet.

Dates are stored as whole numbers. So:

• Subtracting one date from another gives the number of days between them.
• Adding a number to a date moves it forward by that many days.
   

To find how many people signed up in the past week:

• Use COUNTIF() with a condition like ">=" & (endDate - 7).
  Repeat this logic for other ranges like 14 days or 1 month, adjusting the number of days accordingly. For months, use DAY(endDate) to account for varying month lengths.
   

WEEKNUM(date) tells you which week of the year a date falls in. Optionally, specify whether weeks start on Sunday or Monday. Once each row has a week number, use COUNTIF() to summarize signups per week.

Use the TEXT() function to extract parts of a date or time, such as:

• "mmm" for Jan, Feb, etc.
• "dddd" for full weekday names.
  Then use COUNTIF() to analyze signups by day of the week.
   

Rather than creating helper columns for WEEKNUM or TEXT(), you can nest those functions directly into formulas. For example, put =COUNT(IF(TEXT(date,"dddd")="Monday",1,0)) inside an array formula to count Monday signups.

Add date and time together to create a timestamp (e.g., =date + time). Excel treats this as a decimal number that can be formatted to display the full datetime (e.g., 1/1/2022 09:30).

If you need to add hours, use a time format. Instead of +7 (which adds 7 days), use + "7:00" to add 7 hours. This keeps the formula readable and accurate.

To vary the time added (e.g., based on a user-defined delay), reference a cell that holds a value like “5:30” and add that cell to your datetime.

To measure time gaps, subtract one datetime from another. The result will be a fraction of a day. To find the average gap, just average the result column.

If time gaps exceed 24 hours, change the time format to [hh]:mm so Excel doesn’t reset the count after each day.

To see when users sign up during the day, divide the day into segments (e.g., 8 slots of 3 hours each) and count how many signups fall in each.