Want to Extract Start date and End date from various date formats of invoices

Help Studio
1

01 - 10 Jun’25
01/06/2025
01/06/2025
01/06/2025

01/06/2025
01/06/2025
11/06/2025

23/Jan/2024 - 31/Jan/2024
23/Jan/2024 - 31/Jan/2024
07/Jan/2024
23/Jan/2024 - 31/Jan/2024
01/Jan/2024
28/04

#May’25
#Jun’25
01 TO 06 JULY 2025
07 TO 12 JULY 2025
PERIOD 13 TO 18 JULY 2025
1 - 15 JUNE 2025
16 - 30 JUNE 2025
1 - 15 JULY 2025
01-Apr-2025 to 30-Apr-2025
01-Aug-2025 to 31-Aug-2025
01-May-2025 to 31-May-2025
May, 2025
Jun, 2025
Jun-2025
16-JAN-2024 20-JAN-2024
01-OCT-2023 31-DEC-2023
21-JAN-2024 25-JAN-2024
21-JAN-2024 25-JAN-2024
21-JAN-2024 25-JAN-2024
1 - 15 JUNE 2025
16 - 30 JUNE 2025
1 - 15 JULY 2025
01/03/2025 - 31/03/2025
01/03/2025 - 31/03/2025
01/03/2025 - 31/03/2025
01/03/2025 - 31/03/2025
01/05/2025 - 31/05/2025
01/05/2025 - 31/05/2025
01/04/2025 - 15/04/2025
01/05/2025 - 31/05/2025
01/04/2025 - 30/04/2025
01/05/2025 - 31/05/2025
01/06/2025 - 30/06/2025
01/06/2025 - 30/06/2025
01/06/2025 - 30/06/2025
01/05/2025 - 31/05/2025
01/06/2025 - 30/06/2025
01/06/2025 - 30/06/2025
01/06/2025 - 30/06/2025
01/06/2025 - 30/06/2025
01/05/2025 - 31/05/2025
01/06/2025 - 30/06/2025

June 2025
2025
01st - 15th June 2025.
16th - 30th June 2025.
01st - 15th July 2025.
08-14 01 2024
08-14 01 2024
: 15-21 12 2023
08.01.2024
08.01.2024

1 - 15 JULY 2025
2025-04-11
2025-03-01
2025-03-21
2025-03-11
2025-04-01
From: 23-Mar-2024 To: 29-Mar-2024
From: 30-Mar-2024 To: 05-Apr-2024
From: 02-Jun-2025 To: 08-Jun-2025
From: 06-Apr-2024 To: 12-Apr-2024
From: 16-Mar-2024 To: 22-Mar-2024
3-Jan-25
2025-03-01
11th March 2025 Until 20th March 2025
1st April 2025 Until 10th April 2025
11th April 2025 Until 20th April 2025
21st March 2025 Until 31st March 2025
1st March 2025 Until 10th March 2025
2025-04-11
2025-03-11
2025-03-21
2025-04-01
JUL-2025
JUN-2025
JUL-2025

30-APR-24
30-APR-24
30-APR-24

16-Aug-2025
3-Jan-25
3-Jan-25

1st April 2025 Until 10th April 2025
11th April 2025 Until 20th April 2025
1st March 2025 Until 10th March 2025
11th March 2025 Until 20th March 2025
21st March 2025 Until 31st March 2025
2025-04-11
2025-03-11
2025-03-21
2025-04-01
2025-03-01
01-07-2025 to 15-07-2025
1 - 15 JULY 2025
1 - 15 JULY 2025
28/04

This is a Period column of a data table from where i want extracts the start date and end date of invoices. Any way to do that which can handle any of the formats?

2

Hi, @robin.zaman To get the start and end dates from all those crazy invoice date formats you shared:

1.Check for range words like “-”, “to”, “until”. Split the string there.

2.Try parsing each part with DateTime.TryParseExact using formats:

“dd-MM-yyyy”, “dd/MM/yyyy”, “dd MMM yyyy”, “yyyy-MM-dd”, “dd.MM.yyyy”, “dd MMMM yyyy”, “dd’th’ MMM yyyy”, “MMM-yyyy”, “MMM, yyyy”

3.For partial dates like “Jun 2025”, assume start = 1st of month, end = last day of month using:

New DateTime(year, month, DateTime.DaysInMonth(year, month))

4.If only one date parsed, set start = end = that date. Handle unparsed data nicely use TryParse, do not guess.

use TryParseExact in an Invoke or Assign, split strings on words like “to” or “-”, parse each side, and set your start/end dates accordingly.

3

Hello @robin.zaman,

Use Linq query:-

  1. You have a DataTable called dt with a column "Period".

Imports System.Globalization
Imports System.Text.RegularExpressions

’ Define possible date formats
Dim dateFormats As String() = {
“dd/MM/yyyy”, “dd-MM-yyyy”, “dd.MM.yyyy”,
“dd/MM/yy”, “dd-MM-yy”, “dd.MM.yy”,
“dd-MMM-yyyy”, “dd-MMM-yy”, “yyyy-MM-dd”,
“dd MMM yyyy”
}

’ LINQ query
Dim result = From row In dt.AsEnumerable()
Let period = row.Field(Of String)(“Period”).ToLower().Replace(“from:”, “”).Replace(“until”, “|”).Replace(“to”, “|”).Replace(“-”, “|”)
Let dates = Regex.Matches(period, “\d{1,2}[/-.\s]?\w{0,3}[/-.\s]?\d{2,4}”)
.Cast(Of Match)()
.Select(Function(m) m.Value)
.ToArray()
Let startDate = If(dates.Length >= 1, DateTime.ParseExact(dates(0), dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None), Nothing)
Let endDate = If(dates.Length >= 2, DateTime.ParseExact(dates(1), dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None), startDate)
Select New With { .OriginalPeriod = row.Field(Of String)(“Period”), .StartDate = startDate, .EndDate = endDate }