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:
from above value you need to calculate a date & time from today's date & time like below image:
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:
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:
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:
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:
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:
d) Now I need to find what would be the time 10 hours, 10 mins & 10 seconds after current time:
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:
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!
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.