Round up to the nearest 5 seconds

Most people know to round numbers with the ROUND function, but can you use ROUND for times?

On closer inspection, this doesn't seem to be conventional rounding. What should you do?

Round up to the nearest 5 seconds

  • There's a function for this: CEILING, syntax CEILING(number, significance)
  • The function rounds the number up (toward increasing absolute value) to the nearest multiple of the specified significance.
  • In this example, use =CEILING(A2,"0:0:5") to get the desired result.
  • Note the notation for the significance: 5 seconds can be written as "0:0:5"; when using date/time as a constant you must enclose it in quotes.
  • You can also use 1/24/60/60*5. The number 1 represents one day; /24 converts to hours, /60 to minutes, /60 to seconds, and *5 gives 5 seconds.
  • The opposite of CEILING is FLOOR, which rounds down according to the specified significance.

Join the online learning group — over 100 tutorial videos available anytime:

Facebook Group\r\n\r\nThe online learning group is divided into multiple units, including:
  1. Excel functions and formulas
  2. Excel general features
  3. Pivot operations
  4. Excel charts and graphics
  5. Excel VBA
  6. PPT shapes and drawing
  7. Word document processing
  8. One-minute tutorials
  9. Other Office tutorials and resources...
Further divided into multiple topics:
  1. VLOOKUP
  2. Charts
  3. Text functions
  4. Conditional formatting
  5. Array formulas
  6. Text processing
  7. Hide
  8. Find and Replace
  9. Sort and Filter
  10. Numbered lists
  11. Shapes
  12. Consolidation
  13. Error messages
  14. Cell formatting
  15. Text to Columns
  16. Watermark
  17. Input method
  18. Proofing
  19. Name Manager
  20. Page numbers
  21. Printing
  22. External data
  23. Table of Contents
  24. Workshop
  25. Sum functions
  26. Date and Time
  27. PivotTable
  28. Images
  29. Keyboard shortcuts
  30. Merge Cells
  31. Random
  32. Logical functions
  33. Macro functions
  34. Data Validation
  35. Worksheet
  36. Program Settings
  37. Tables
  38. Lookup functions
  39. Duplicates
  40. PDF
  41. Go To
  42. Comments
  43. Special cells
  44. Copy & Paste
  45. Lock
  46. Dynamic Charts
  47. Layout
  48. Headers and Footers, etc.

Demo Video:

Application process:

  1. Payment
  2. We will send you the Facebook Group link.
  3. Answer a few questions before joining
  4. We'll complete it after verification.

Fees:

$399 $199 [Join now, save $200]

The End

, , ,

•

,
WhatsApp Support

CELL COLUMN COUNTIF Excel Excel formulas Excel functions FILTER FIND IF INDEX LEFT LOOKUP MATCH MAX Microsoft Excel MID Office suite PowerPoint RIGHT ROW SEQUENCE SUM SUMIF SUMPRODUCT Unique VLOOKUP Windows Windows 10 Word Find and Replace Line Break number formatting Text to Columns date calculation Time Flash Fill Conditional Formatting Column background removal wildcards data analysis data cleaning Error messages Array formulas Color

Comprehensive Technical Support Solutions

We provide a range of technical support solutions to meet your needs.
Explore our core services:

man and woman wearing headphones while working in the office

Real-Time Technical Support

Learn More

Get on-demand assistance with Microsoft Excel, Word and PowerPoint. Send your question via WhatsApp, and we will help you resolve it promptly.

serious diverse students looking at laptop

Online Learning Group

Learn More

Join our Online Learning Group Facebook page. Explore more than 200 tutorials and a comprehensive library of instructional videos at your own pace.

woman in yellow blazer doing a presentation

Corporate Training

Learn More

Build your team’s skills with tailored corporate training programmes. Our courses are designed around your organisation’s needs and equip employees with practical, up-to-date tools.