Hello, I tried custom formatting, and other types, but my excel is refusing to allow me to use days:
I want to do this: For example: A1I Enter myself): 5 Days, 8 Hours, 0 Minutes, 0 Seconds so: 05:08:00:00 , I want A2 to convert that into seconds. So B2 should automatically list 460800.
The issue is my A1 is not recognizing as days hours minutes, it goes into hours minutes seconds, and sometimes it even puts a date like 1/08/1980.. and then even an AM or PM after that time. Any help?
I would recommend splitting it into 2 cells, one for days and one for time.
Can you post an example book ?
Its not letting me upload an attachment. Everytime i click on the little attachment symbol on both mozila and chrome. nothing happens.. a little why bar and thats it.
With your value in A1, this in B1:
=(LEFT(A1,2)*24*60*60)+(MID(A1,4,2)*60*60)+(MID(A1,7,2)*60)+RIGHT(A1,2)
Format column B as a number to 0 decimal places.
Last edited by AliGW; 08-07-2017 at 12:16 PM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
But its pretty simple honestly, no attachment needed: I want to put in A1: 5:08:00:00 (5 Days, 8 Hours, 0 Minutes, 0 Seconds).. and A2 I want to convert that into seconds. I have another document where i was able to successfully convert seconds into days, but this document is refusing to go days into seconds.
See post #4.
You can put the formula into A2 and format that cell as a number.
thank you Ali, you are a savor: Now , would you be able to just explain this for not so excel pro's What does the "Left" and "Mid" and "Right" here reference too.
These are the positions of the couplets in cell A1:
=LEFT(A1,2) is 05
=MID(A1,4,2) is 08 (two characters starting at position 4 in the text string)
and so on ...
Use the evaluate formula feature on the formula ribbon to step through and see what it's doing.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Or if you put the number of days in A1, and the hh:mm:ss in B1 then it's just
=(A1+B1)*8640
would you be able to re-write that equation for me with the Day Being in B3: and Seconds being in C3? I am having trouble re-writing it.
Originally Posted by AliGW
Yea, I knew this, thats the easy way out, but i needed it to be formatted exactly like i wrote above with the DD:HH:MM:SS
Why? I thought the whole point was that the whole value would be in one cell?
What format are B3 and C3 in?
Why ?but i needed it to be formatted exactly like i wrote above with the DD:HH:MM:SS
Are you just deteremined to have it in one cell, or is there a real reason to not use 2 cells ?
There is nothing wrong with using multiple cells to accomplish a task. Excel gives you over 17 billion cells in a sheet to work with. It's ok to use few of them.
Anyway, you could do this, if that is always the exact format.
=(LEFT(A1,2)+RIGHT(A1,8))*86400
Last edited by Jonmo1; 08-07-2017 at 12:39 PM.
In B3: I am putting DD:HH:MM:SS , and then in C3.. I want the Result in seconds.
Well, just change each reference from A1 to B3, then. In C3:
=(LEFT(B3,2)*24*60*60)+(MID(B3,4,2)*60*60)+(MID(B3,7,2)*60)+RIGHT(B3,2)
and make sure that C3 is formatted as a number with no decimal places.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Thank you very much!
You're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks