Hello All,
I want to convert below values in following format "xx days xx hours xx minutes"
-2d 10h
-27min
-2h 29m
-1w 1d
-1w 1d
-2d 12h
-2d 8h
-1d 18h
0min
0min
1min
-1w 2d
-3h 59m
Can anyone please provide me the formula.
Hello All,
I want to convert below values in following format "xx days xx hours xx minutes"
-2d 10h
-27min
-2h 29m
-1w 1d
-1w 1d
-2d 12h
-2d 8h
-1d 18h
0min
0min
1min
-1w 2d
-3h 59m
Can anyone please provide me the formula.
Last edited by AliGW; 10-16-2018 at 09:11 AM.
Welcome to the forum!
This forum requires you to give more informative thread titles. By way of an example, I have changed yours for you this time. Please bear this advice in mind in future. Thanks!
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.
thank you for giving proper heading
Hi,
Not sure what the Dash " - " at the beginning of the data means (does it mean negative?, or it doesn't mean anything?)
Assuming the Dash means nothing, here's one way:
Excel 2016 (Windows) 64 bit
A B 1-2d 10h 2 day(s) 10 hour(s) 2-27min 27 minute(s) 3-2h 29m 2 hour(s) 29 minute(s) 4-1w 1d 1 week(s) 1 day(s) 5-1w 1d 1 week(s) 1 day(s) 6-2d 12h 2 day(s) 12 hour(s) 7-2d 8h 2 day(s) 8 hour(s) 8-1d 18h 1 day(s) 18 hour(s) 90min 0 minute(s) 100min 0 minute(s) 111min 1 minute(s) 12-1w 2d 1 week(s) 2 day(s) 13-3h 59m 3 hour(s) 59 minute(s)
Sheet: Sheet104
Excel 2016 (Windows) 64 bit
B 1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"w"," week(s)"),"d"," day(s)"),"h"," hour(s)"),"min","m"),"m"," minute(s)")
Sheet: Sheet104
Formula copied down.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks