Calculate date difference without datedif function

[TheWes]

Hi guys,

I want to calculate the difference between a start date of absence and recovery date.
Below is a snip from my current dataset.

few things:
1. Datedif doesnt
2. nor subtracting both from each other and divide by 7.

I probaly need to convert them. Will follow-up but could use some tips
Thank you!

Week of Day First Start Date Absence	Week of Recovery Date Absence
June 10, 2024	00/01/1900
November 27, 2023	December 2, 2024
May 20, 2024	July 1, 2024
February 12, 2024	February 19, 2024
July 17, 2023	July 24, 2023

 

[etaf]

seems like the dates are text and NOT real dates
if you change the format to general , do you get a number ?

and you are using 2016 version

 

[Cubist]

This is not a real date: 00/01/1900, but DATEDIF works fine.

Book1
	A	B	C	D
1	Week of Day First Start Date Absence	Week of Recovery Date Absence	Days	Divide by 7
2	6/10/2024	00/01/1900	#VALUE!	#VALUE!
3	11/27/2023	12/2/2024	371	53
4	5/20/2024	7/1/2024	42	6
5	2/12/2024	2/19/2024	7	1
6	7/17/2023	7/24/2023	7	1
Sheet4

Cell Formulas
Range	Formula
C2:C6	C2	=DATEDIF(--A2,--B2,"d")
D2:D6	D2	=C2/7

 

[TheWes]

seems like the dates are text and NOT real dates
if you change the format to general , do you get a number ?

and you are using 2016 version

yes I am using 2016 Excel.

So I changed the output of the data and I changed it to general but I will seem to at times weird output:
- the first 2 columns is the raw output
- the last 2 columns is when I put format on general. As you can tell sometimes it gives me a number and sometimes just the date. The date is in mm/dd/yyyy form, which I am not able to change it seems.

Any thoughts?
Thank you
Araw dateset:

Day First Start Date Absence	Recovery Date Absence	Day First Start Date Absence	Recovery Date Absence
9/16/2018	12/21/2023	9/16/2018	12/21/2023
12/25/2023	12/30/2023	12/25/2023	12/30/2023
07/05/2024	0	45419
07/07/2024	07/10/2024	45480	45572
10/31/2024	11/04/2024	10/31/2024	45393
11/26/2023	0	11/26/2023
03/08/2024	03/10/2024	45507	45568
06/05/2024	06/09/2024	45418	45541
10/12/2024	10/21/2024	45636	10/21/2024
02/01/2023	02/06/2023	44928	45079
4/26/2023	0	4/26/2023
01/11/2023	1/26/2023	45231	1/26/2023

 

[etaf]

thats probably because when it imports it recognises the dates and some not
so if you have a DD/MM/YYYY setting then things like 9/16/23 - would not work as it thinks its the 16th Month
hence why a mixture of dates
Also
if the date is 12th March 24 - 12/3/24 - in UKK format - That may be read as 3rd december in US - so the Date is wrong when its imported

What are you using to import
IT maybe worth using the import wizard and making sure ALL the dates are imported as TEXT - so you dont get WRONG dates displayed
and then using a combination of MID() left() Right() and & with a datevalue() to convert

I'm assuming US format - as would only expect a few days between column A and B

can you confirm what format you use
MM/DD/YYYY

 

[MARK858]

@TheWes I'm a bit confused, does your actual data for the first 2 columns appear as you have them in post 4 i.e. numbers separated by a / or are some of them words and numbers as in post 1 (if they are text then they wouldn't have changed to numbers separated by a /)