Converts a time duration in minutes to a readable format (e.g., 1 hr 30 min)
=IF(base_unit = "days",
IF(INT(duration/365) > 0, INT(duration/365) & " yr ", "") &
IF(INT(MOD(duration, 365)/30.44) > 0, INT(MOD(duration, 365)/30.44) & " mo ", "") &
IF(ROUND(MOD(duration, 30.44), 0) > 0, ROUND(MOD(duration, 30.44), 0) & " d ", ""),
IF(base_unit = "hours",
IF(INT(duration/8760) > 0, INT(duration/8760) & " yr ", "") &
IF(INT(MOD(duration, 8760)/730) > 0, INT(MOD(duration, 8760)/730) & " mo ", "") &
IF(INT(MOD(duration, 730)/24) > 0, INT(MOD(duration, 730)/24) & " d ", "") &
IF(MOD(duration, 24) > 0, MOD(duration, 24) & " hr ", ""),
IF(base_unit = "minutes",
IF(INT(duration/525600) > 0, INT(duration/525600) & " yr ", "") &
IF(INT(MOD(duration, 525600)/43800) > 0, INT(MOD(duration, 525600)/43800) & " mo ", "") &
IF(INT(MOD(duration, 43800)/1440) > 0, INT(MOD(duration, 43800)/1440) & " d ", "") &
IF(INT(MOD(duration, 1440)/60) > 0, INT(MOD(duration, 1440)/60) & " hr ", "") &
IF(MOD(duration, 60) > 0, MOD(duration, 60) & " min ", ""),
IF(base_unit = "seconds",
IF(INT(duration/31536000) > 0, INT(duration/31536000) & " yr ", "") &
IF(INT(MOD(duration, 31536000)/2628000) > 0, INT(MOD(duration, 31536000)/2628000) & " mo ", "") &
IF(INT(MOD(duration, 2628000)/86400) > 0, INT(MOD(duration, 2628000)/86400) & " d ", "") &
IF(INT(MOD(duration, 86400)/3600) > 0, INT(MOD(duration, 86400)/3600) & " hr ", "") &
IF(INT(MOD(duration, 3600)/60) > 0, INT(MOD(duration, 3600)/60) & " min ", "") &
IF(MOD(duration, 60) > 0, MOD(duration, 60) & " sec", "")
))))This function provides a granular breakdown of time for activities or projects. This detailed time format is especially useful in environments like project management, long-term planning, and reporting, where understanding the duration across different scales is essential. The inputs need to be in minutes. For example, if "A1" contains "2,000,000" "minutes", the function "=DURATION_FORMAT(A1)" would output something like "3 yr 9 mo 20 d 21 hr 20 min", offering a comprehensive view of the time duration. This enables precise and understandable time statements that can help in evaluating time durations.
Use the inputs below to create DURATION_FORMAT as a reusable custom function in Google Sheets.
Learn how to add custom functions to Google SheetsDURATION_FORMAT
Converts a time duration in minutes to a readable format (e.g., 1 hr 30 min)
=IF(base_unit = "days", IF(INT(duration/365) > 0, INT(duration/365) & " yr ", "") & IF(INT(MOD(duration, 365)/30.44) > 0, INT(MOD(duration, 365)/30.44) & " mo ", "") & IF(ROUND(MOD(duration, 30.44), 0) > 0, ROUND(MOD(duration, 30.44), 0) & " d ", ""), IF(base_unit = "hours", IF(INT(duration/8760) > 0, INT(duration/8760) & " yr ", "") & IF(INT(MOD(duration, 8760)/730) > 0, INT(MOD(duration, 8760)/730) & " mo ", "") & IF(INT(MOD(duration, 730)/24) > 0, INT(MOD(duration, 730)/24) & " d ", "") & IF(MOD(duration, 24) > 0, MOD(duration, 24) & " hr ", ""), IF(base_unit = "minutes", IF(INT(duration/525600) > 0, INT(duration/525600) & " yr ", "") & IF(INT(MOD(duration, 525600)/43800) > 0, INT(MOD(duration, 525600)/43800) & " mo ", "") & IF(INT(MOD(duration, 43800)/1440) > 0, INT(MOD(duration, 43800)/1440) & " d ", "") & IF(INT(MOD(duration, 1440)/60) > 0, INT(MOD(duration, 1440)/60) & " hr ", "") & IF(MOD(duration, 60) > 0, MOD(duration, 60) & " min ", ""), IF(base_unit = "seconds", IF(INT(duration/31536000) > 0, INT(duration/31536000) & " yr ", "") & IF(INT(MOD(duration, 31536000)/2628000) > 0, INT(MOD(duration, 31536000)/2628000) & " mo ", "") & IF(INT(MOD(duration, 2628000)/86400) > 0, INT(MOD(duration, 2628000)/86400) & " d ", "") & IF(INT(MOD(duration, 86400)/3600) > 0, INT(MOD(duration, 86400)/3600) & " hr ", "") & IF(INT(MOD(duration, 3600)/60) > 0, INT(MOD(duration, 3600)/60) & " min ", "") & IF(MOD(duration, 60) > 0, MOD(duration, 60) & " sec", "") ))))
duration
base_unit
The total duration according to the selected base unit
90
What the base units of the duration are. Either "seconds", "minutes", "hours", or "days" to define the base unit of the input duration
"minutes"
Other functions in the same categories: Date & Time Operations, Formatting & Display
Formats a number dynamically as thousands, millions, billions, or trillions.
Determine if a date falls within a start and end date
Returns TRUE if two date ranges overlap
Formats a person's display name from first/last and other name parts
Calculates the difference between two dates in whole weeks.
Returns TRUE if a date falls on Monday through Friday
Named functions enable the creation of custom, reusable formulas that mimic built-in functions, streamlining calculations and data manipulations. These functions simplify complex formulas, making spreadsheets more readable and less prone to errors. By encapsulating intricate logic within a single function call, they enhance consistency across your data. Utilizing named functions reduces the need to write lengthy formulas repeatedly, thereby improving workflow efficiency and productivity in data analysis and management.
Download and import — fastest way to add the function but does not include argument descriptions and examples
Follow these simple steps to download and import a function into your spreadsheet. This method is the quickest and easiest way to add the named function to your Google Sheets document, but will not include the argument description and examples.
Once added, the function will be ready to use in your document like any other built-in function. Simply type the function name and provide the required inputs to use it in your calculations.
Copy and paste — copy and paste each property one at a time
Follow these simple steps to integrate a custom named function into your spreadsheet:
Once added, the function will be ready to use in your document like any other built-in function. Simply type the function name and provide the required inputs to use it in your calculations.