工期概率计算_如何从文本和数字单元格值计算工期

工期概率计算

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.

我当时在EE上参加了一个MS-Excel问题，发现这个问题非常有趣且充满挑战，我认为我应该与每个人分享以备将来参考。

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

假设您在A2中的单元格值为“ 10d，10h，10m，10s”，如下图所示：

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:

1.首先让我们使用“替换公式”删除单元格的所有文本内容，我们必须使用此公式4次才能删除4个文本，例如d，h，m，s，如下图所示：

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:

2.现在，我们需要在每个逗号后分割所有数字。 有很多公式可以做到这一点，但是我使用的是非常有用的函数，该函数是在某些站点上发现的。 下面是我使用的功能：

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:

使用上面的功能，我想将天，小时，分钟和秒划分为4个不同的列，如下图所示：

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:

3.现在是棘手的步骤，在这一步中，我们需要拆分过程，但应合并最终结果。a）首先让日期部分获取，我需要根据我们的位置，找到今天之前10天的日期。上面的计算，很简单，我将只使用下面的公式：

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:

b）现在，我需要根据我们上面的计算，找到今天之后10天的日期，就像3.a）一样，而不是扣除，我在下面添加：

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

您可以通过替换“ 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:

c）现在让我们对时间做同样的事情，我需要找到比当前时间早10小时10分10秒的时间：

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

d）现在，我需要查找当前时间之后10小时10分10秒的时间：

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.

您也可以在此处更改时间格式，方法是将“ hh：mm：ss AM / PM”替换为12小时，或者将“ hh：mm：ss”替换为24小时时间格式。

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:

4.您可能想知道，我会合并日期和时间公式以进入一个单元格，说实话，我不确定，我是否能够获得正确的输出:)。 无论如何，这是我在此步骤中要结合日期和时间的操作：

Start Date & Time

开始日期和时间

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

如果本文对EE成员有用且有价值，请不要忘记按“ Thumb's Up”按钮。

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

它还以表格的形式向我提供了积极的反馈。 谢谢！

翻译自: https://www.experts-exchange.com/articles/29164/How-to-calculate-Duration-from-text-and-number-cell-value.html

工期概率计算