Blue Prism中文本日期时间计算与格式适配问题咨询

Hey there! Let's work through this date calculation problem together. The core issue here is that you can't directly subtract text-based dates—we first need to convert your day/month/year formatted strings into actual date values that Excel or VBA can recognize and compute with. Here are two reliable approaches:

方法1：Excel公式（无需编程） #

For a date text like 11/3/2017 12:00:00 AM in cell A1, use this formula to convert it to a standard date value:

=DATE(
    RIGHT(LEFT(A1,FIND(" ",A1)-1),4),  // Extract the 4-digit year
    MID(LEFT(A1,FIND(" ",A1)-1),FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1),  // Extract month
    LEFT(LEFT(A1,FIND(" ",A1)-1),FIND("/",A1)-1)  // Extract day
)

This formula automatically ignores the time portion and works regardless of whether day/month are 1 or 2 digits (e.g., 1/3/2017 or 11/3/2017).

To calculate the number of leave days between two dates (A1 = start, B1 = end), wrap the conversion in an absolute value to avoid negative numbers:

=ABS(
    DATE(RIGHT(LEFT(A1,FIND(" ",A1)-1),4),MID(LEFT(A1,FIND(" ",A1)-1),FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1),LEFT(LEFT(A1,FIND(" ",A1)-1),FIND("/",A1)-1))
    -
    DATE(RIGHT(LEFT(B1,FIND(" ",B1)-1),4),MID(LEFT(B1,FIND(" ",B1)-1),FIND("/",B1)+1,FIND("/",B1,FIND("/",B1)+1)-FIND("/",B1)-1),LEFT(LEFT(B1,FIND(" ",B1)-1),FIND("/",B1)-1))
)

方法2：VBA自定义函数（更简洁灵活） #

If you need to do this frequently, a custom VBA function will save you time:

Function ConvertDMYToDate(dateText As String) As Date
    ' Split off the time portion to focus on the date
    Dim datePart As String
    datePart = Split(dateText, " ")(0)
    
    ' Split day, month, year using the slash separator
    Dim dateComponents() As String
    dateComponents = Split(datePart, "/")
    
    ' Convert to a proper date value (year, month, day order)
    ConvertDMYToDate = DateSerial(CInt(dateComponents(2)), CInt(dateComponents(1)), CInt(dateComponents(0)))
End Function

Once you add this to your VBA editor, use it in Excel like any other function:

=ABS(ConvertDMYToDate(A1) - ConvertDMYToDate(B1))

This handles variable-length date strings flawlessly since Split doesn't care about how many digits each component has.

Quick note on "removing irrelevant content" #

If you just want to clean up the text (e.g., strip the time), use this formula to extract only the date part:

=LEFT(A1,FIND(" ",A1)-1)

But remember—this still gives you a string, not a computable date. You'll still need to convert it using one of the methods above to calculate days.

内容的提问来源于stack exchange，提问作者Oleander93