Final exam guide for B1: Text and date functions and analysis tools

If you're having trouble with your B1 - Intermediate certification exercises, make sure you can answer "Yes" to these questions before reaching out to a teacher:

• If you are working with text functions (MID, LEFT): Did you accurately count the spaces and characters before defining the starting position?

• If you are working with DATEDIF: Did you put the unit arguments ("Y", "YM", "MD") in double quotes and "lock" the current date cell with $?

• If you are creating PivotTables: Did you uncheck the "Autofit column widths on update" option?

• If you are using Goal Seek: Did you type the profit value as a clean number without any periods or commas?
  
  

If you’ve answered all these questions and you’re still having issues, check the following guide with common errors in the B1 final exam and how to solve them: 

Problem #1: When using the MID function, it doesn't show the correct characters.

Solution: This error usually stems from miscounting the "Start_num." Remember that position 1 is the very first character on the left. Any space or punctuation mark counts as a position. 

Problem #2: In the TEXTJOIN exercise, the data is all bunched together

Solution: Check the first argument of the function: the delimiter. To ensure names and numbers have a space between them, you must include a space inside double quotes: =TEXTJOIN(" ", , C5:E5). If you leave the quotes empty (""), Excel will not provide the required separation.

Problem #3: DATEDIF doesn't appear in the function list or returns an error when dragged.

Solution: DATEDIF is a "hidden" legacy function and does not show a syntax tooltip. You must enter the arguments manually: =DATEDIF(start_date, end_date, "unit"). To prevent errors when dragging the formula, use an absolute reference for the end date by pressing F4 (e.g., $B$32).

Problem #4: I calculated a vacation return date by adding days (+), but the date is wrong. 

Solution: You likely added days directly to the date without accounting for weekends or holidays. For this, use the WORKDAY function (not NETWORKDAYS). This function specifically calculates a future date by excluding non-working days.

Problem #5: Goal Seek isn't giving me the exact expense amount.

Solution: Ensure you do not type manual thousand separators (periods or commas) in the dialog box. If the exercise asks to reach a profit of 10,800, type the clean number: 10800. Also, verify that the "By changing cell" is actually the Expenses cell (C58) and not the Income cell.

Problem #6: My PivotTable has empty cells, and the column width keeps changing on its own.

Solution: This exam has very specific configuration requirements. For empty cells, go to PivotTable Options and type 0 in the "For empty cells show" box. For column widths, you must uncheck the box "Autofit column widths on update." If you don't, your custom formatting will be lost every time you refresh.

Problem #7: I can't select multiple non-consecutive items in a Slicer. 

Solution: To select multiple separate items, hold down the Ctrl key while clicking each one. Alternatively, you can toggle the "Multi-Select" icon in the top-right corner of the Slicer.

Are you still having trouble with a specific B1 task? Check out our related article on common errors for more tips on mastering function logic and data analysis!