<

[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x

How to calculate Duration from text and number cell value

Published on
6,938 Points
738 Views
27 Endorsements
Last Modified:
Shums
I Love Challenges in MS-Excel
Where there is a will there are many ways. Finding one that works for you is the challenge!
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.

I was attending to one of MS-Excel question here on EE and found this question very interesting and challenging, thought I should share with every one for future reference.


Suppose you have a cell value "10d, 10h, 10m, 10s" in A2, like below image:

A2.JPG


from above value you need to calculate a date & time from today's date & time like below image:


G2.JPG

Lets find out by following steps:


1. First lets remove all text contents of the cell by using Substitute Formula, we have to use this formula 4 times to remove 4 texts like d, h, m, s like below image:

B2-Formula.JPG B2-Value.JPG



2. Now we need to split all numbers after each commas. There are many formulas to do that, but I am using very useful function, which I found on some site. Below is the function I used:

Function FindWord(Source As String, Position As Integer)
Dim arr() As String
arr = VBA.Split(Source, ",") 'Change the string which suits you
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = "," 'Change the string which suits you
Else
    FindWord = arr(Position - 1)
End If
End Function

Using above function, I am gonning to split days, hours, mins and seconds in 4 different columns like below image:


Days
C2-Formula.JPG C2-Value.JPGHours
D2-Formula.JPG D2-Value.JPGMinutes
E2-Formula.JPG E2-Value.JPGSeconds
F2-Formula.JPG F2-Value.JPG


You would have seen, I have changed the last number to split as per appearance of commas.


3. Now the tricky steps, in this step we need to split our process but end result should be combined.a) Lets get the date part first, I need to find what would be the date 10 days earlier to today, as per our above calculation, very simple, I will just use below formula:


G2-Formula-1.JPG G2-Value-1.JPG


b) Now I need to find what would be the date 10 days after today, as per our above calculation, same steps like 3.a) instead of deducting, I am adding below:


H2-Formula-1.JPG H2-Value-1.JPG


You may change the date format as you like by replacing "mm/dd/yy"

c) Now lets do the same for Time, I need to find what would be the time 10 hours, 10 mins & 10 seconds earlier to current time:


G3-Formula.JPG G3-Value.JPG


d) Now I need to find what would be the time 10 hours, 10 mins & 10 seconds after current time:


H3-Formula.JPG H3-Value.JPG


Here also you may change the time format as you like by replacing "hh:mm:ss AM/PM" for 12 hours or "hh:mm:ss" for 24 hours time format.


4. You might be wondering, I would have merged both date & time formula to get in one cell, honestly, I was not sure, I will be able to get the right output :). Anyway this is what I am gonna do in this step to combine date & time:


Start Date & Time


G2-Formula-2.JPGEnd Date & Time
H2-Formula-2.JPGTo get final result:
G2-2.JPG



Please do not forget to press the "Thumb's Up" button if this article was helpful and valuable for EE members.

It also provides me with positive feedback in the form. Thanks!

27
Comment
Author:Shums
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 

Expert Comment

by:Enrique A. Santos KM4ZQC
Outstanding!
3
 

Expert Comment

by:Rahul Roy
Pretty useful function and quite helpful
3
 

Expert Comment

by:Jennifer Liu
Very useful to know!
3
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Expert Comment

by:ADRIANA P
Very Insteresting !
2
 

Expert Comment

by:Steve Bez
Thanks for making it so easy to understand!
2
 

Expert Comment

by:abbey Law
Wow! This is impressive and so clear to understand. Thank you for sharing your invaluable knowledge :)
2
 

Expert Comment

by:Aamir Hussain
A simple and excellent solution of the complicated question. Well done Mr.Shums :-)
2
 

Expert Comment

by:Brendan Wilson
This is an excellent article. I appreciate you taking the time to write this.
1
 

Expert Comment

by:Judy Deo
Thanks for taking the time to post this in such detail. Appreciate it.
1

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Join & Write a Comment

Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month