Modify date format to use NETWORKDAYS VBA

[SAMCRO2014]

Hi. I am trying to format my dates from YY.MM.DD to a usable format to use with the NETWORKDAYS function. The report I exported only comes out with the dates as YY.MM.DD which NETWORKDAYS function does not like. I have over 50K of data with this data format. I tried creating a loop but I just can't seem to get the date format correct.

Any advice would be appreciated.

 

[WaterGypsy]

If you use this code in the NETWORKDAYS formula instead of the date field (changing A1 to whatever the cell you want to reference is) it should work:

DATE(LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))

 

[SAMCRO2014]

It is giving me the incorrect day count. It should be 5 days but the formula is counting 4 days.

H2 = 18.09.17 (start date)
I2 = 18.09.21 (end date)

=NETWORKDAYS((DATE(LEFT(H2,2),MID(H2,4,2),RIGHT(H2,2))),(DATE(LEFT(I2,2),MID(I2,4,2),RIGHT(I2,2))),0)

 

[Tetra201]

It's because with such an approach, the dates are converted into 1918-09-17 and 1918-09-21.

You can convert a column of your data into Excel dates by using the Text to Columns Wizard and selecting YMD as the column data format on Step 3 of 3.

 

[WaterGypsy]

It is giving me the incorrect day count. It should be 5 days but the formula is counting 4 days.

H2 = 18.09.17 (start date)
I2 = 18.09.21 (end date)

=NETWORKDAYS((DATE(LEFT(H2,2),MID(H2,4,2),RIGHT(H2,2))),(DATE(LEFT(I2,2),MID(I2,4,2),RIGHT(I2,2))),0)

Well ..... taking Tetra201 into account you may need to use

=NETWORKDAYS((2000+DATE(LEFT(H2,2),MID(H2,4,2),RIGHT(H2,2))),(2000+DATE(LEFT(I2,2),MID(I2,4,2),RIGHT(I2,2))),0)+1


The 2000+ will make the century correct (for the next 80 odd years anyway) and as NETWORKDAYS gives the difference between the dates you need to add another day hence +1 at the end to make it inclusive of start date

 

[SAMCRO2014]

Thank you Everyone!

 

[Phuoc]

One way:

=NETWORKDAYS(SUBSTITUTE(20&H2,".","/"),SUBSTITUTE(20&I2,".","/"))

 

[SAMCRO2014]

I thought my problem was fixed but it is not

If my start date falls on a weekend, such as 2018.04.01 - it gives me a day too much without the "+1"
If my start date and end date are the same - it gives me no data but I need it to say one

Start Date End Date Calculates Should be
2018.04.01 2018.06.13 54 days 53 days
2018.07.27 2018.07.27 0 days 1 day

I have 50K lines of data and I cannot go through each one individually. Is there another function I can use in my macro?

 

[WaterGypsy]

Put briefly then, I think you need to check the dates for being a weekend (I usually check for "S" in the first character of the day, but others use the day number) .... then only add 1 if both are not a weekend