This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
How can I convert 141899 seconds into Days, hours, Mins, secs ?
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Hi. Excel's units are days. 1 day = 86400 seconds
So, 141899 / 86400 = 1.64235 days.
Then format with whatever you like:
d "day+" hh:mm:ss
Hi Harry,
You may use the convert formula for easy conversion to year, days, minutes and seconds.
Follow on the formula format below.
// Happy to serve
Best Regards,
Fidz
Excel does dates relatively well, within limits. Durations are a little tricky.
.
This article shows an alternate approach to the technique Rand provided
Time durations are possible in Excel
https://office-watch.com/2021/entering-time-durations-in-excel/
15 May 2021
” How can I enter a duration in Excel with the format days:hours:minutes:seconds ?
If I enter 5:32 Excel converts that to 5:32am but I want to record 5 hours and 32 minutes.”
Time durations aren’t very clear in Excel, but they are possible.
The time features are mostly aimed towards date and time so the duration options aren’t obvious.
FYI here are some articles and notes on formatting dates and times in Excel
Excel Date and Time Formatting codes
https://www.myonlinetraininghub.com/excel-date-and-time-formatting
October 20, 2017 by Mynda Treacy
Even though dates and time are actually stored as a regular number known as the date serial number, we can make use of extensive Excel date and time formatting options to display them just the way we want.
We can access some quick date and time formats from the Home tab > in the Number group
.
How to change date format in Excel and create custom formatting
https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/
by Svetlana Cheusheva | updated on November 2, 2021
The first part of our tutorial focuses of formatting dates in Excel and explains how to set the default date and time formats, how to change date format in Excel, how to create custom date formatting, and convert your dates to another locale.
Along with numbers, dates and times are the most common data types people use in Excel. However, they may be quite confusing to work with, firstly, because the same date can be displayed in Excel in a variety of ways, and secondly, because Excel always internally stores dates in the same format regardless of how you have formatted a date in a given cell.
Knowing the Excel date formats a little in depth can help you save a ton of your time. And this is exactly the aim of our comprehensive tutorial to working with dates in Excel. In the first part, we will be focusing on the following features:
· How to convert date to number in Excel
· Default date format in Excel
· How to change date format in Excel
· How to convert date format to another locale
· How to create custom date and time formats
· Excel date format not working - fixes and solutions
.
You may also be interested in
· How to conditionally format dates in Excel
· How to format cells in Excel
· How to insert dates in Excel - add today's date, auto fill a column with dates
· How to calculate days between dates in Excel
.
How to create a custom Excel date format for another locale
If you want to display dates in another language, you have to create a custom format and prefix a date with a corresponding locale code. The locale code should be enclosed in [square brackets] and preceded with the dollar sign ($) and a dash (-). Here are a few examples:
· [$-409] - English, Untitled States
· [$-1009] - English, Canada
· [$-407] - German, Germany
· [$-807] - German, Switzerland
· [$-804] - Bengali, India
· [$-804] - Chinese, China
· [$-404] - Chinese, Taiwan
You can find the full list of locale codes on this blog.
.
(Enhanced) Complete List of Excel Date and Time Formatting Codes
I’ve added a couple of codes to the ones provided in the article: Rohnski
| Code | Description | Example (January 1, 2005) |
|---|---|---|
| m | Month number without a leading zero | 1 |
| mm | Month number with a leading zero | 01 |
| mmm | Month name, short form | Jan |
| mmmm | Month name, full form | January |
| mmmmm | Month as the first letter | J (stands for January, June and July) |
| d | Day number without a leading zero | 1 |
| dd | Day number with a leading zero | 01 |
| ddd | Day of the week, short form | Mon |
| dddd | Day of the week, full form | Monday |
| yy | Year (last 2 digits) | 05 |
| yyyy | Year (4 digits) | 2005 |
When setting up a custom time format in Excel, you can use the following codes.
| Code | Description | Displays as |
|---|---|---|
| h | Hours without a leading zero | 0-23 |
| [h] | Displays elapsed time in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, use a number format that resembles [h]:mm:ss or [h]:mm | |
| hh | Hours with a leading zero | 00-23 |
| m | Minutes without a leading zero | 0-59 |
| [m] | Displays elapsed time in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss. | |
| Mm | Minutes with a leading zero | 00-59 |
| s | Seconds without a leading zero | 0-59 |
| [s] | Displays elapsed time in seconds. If you are working with a formula that returns a time in which the number of seconds exceeds 60, use a number format that resembles [ss]. | |
| ss | Seconds with a leading zero | 00-59 |
| .0 | Tenths of a second | |
| .00 | Hundredths of a second | |
| .000 | Thousandsths of a second | |
| AM/PM<br><br>am/pm<br><br>A/P<br><br>a/p | Periods of the day <br>(if omitted, 24-hour time format is used) | AM or PM |
.
Date Examples
| Format | Displays as |
|---|---|
| d. mmmm yyyy | 3. January 2012 |
| dd. “text” | 03. text |
| dd. mm. yyyy dddd | 03. 01. 2012 Tuesday |
| dd/mm/yyyy | 03/01/2012 |
| ddd, mmmm dd, yyyy hh:mm:ss | Tue, January 13, 2015 13:03:00 |
| dddd | Tuesday |
| dddd, m/d/yy h:mm AM/PM | Tuesday, 1/13/15 1:03 PM |
| dddd, mmmm dd yyyy | Tuesday, January 03 2012 |
| dd-mmm-yy | 13-Jan-15 |
| ddmmyy | 030112 |
| dd-mm-yy | 03-01-12 |
| d-m-yyyy | 3-1-2012 |
| m/dd/yy | 1/13/15 |
| mm/dd/yyyy | 01/13/2015 |
| mmmm | January |
| mmmm dd, yyyy | January 03, 2012 |
.
Time Examples
Let’s have the time 6:25:31
| Code | Time |
|---|---|
| hh.mm | 06.25 |
| Hhmm | 0625 |
| [h]:mm:ss.00 | 6:25.31.00 |
| h:mm:ss AM/PM | 6:25:31 AM |
| hh “hours and” mm “minutes” | 06 hours and 25 minutes |
| [m] | 385 (the number of minutes since 00:00:00) |
| [s] | 23131 (the number of seconds since 00:00:00) |
.
Hi Harry,
You can try using the MS Excel to try the conversion.
You then use custom formatting to display the days as well as the hours, minutes and seconds.
Kindly follow the procedure provided on the link below on how to convert using MS Excel.
https://www.automateexcel.com/formulas/convert-seconds-to-minutes-hours/
https://www.extendoffice.com/documents/excel/820-excel-convert-hour-minute-second-date.html
https://www.extendoffice.com/documents/excel/3300-excel-convert-seconds-to-hh-mm-ss.html
“Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.”
Best Regards,
Oloduku