Oct 08 2018 10:42 AM
Hello Community,
I have downloaded a report in csv file from our company service software. I have saved the file in "xlsx" format. I am trying to count days between the two dates but the results return with "#VALUE!" error. I have tried hard to format the dates in the "dd.mm.yyyy" format using format cell but nothing happening. How do i calculate the number of days between the two dates. I even tried MS expert advice through MS Support feature available with in the excel application. But they failed to provide any solution. Hoping to find a solution in the community.
Attached is the file i am trying to calculate number of days. Please someone help me.
Oct 08 2018 10:59 AM
SolutionHi Syed,
This is because that's dates are formatted as texts not as numeric dates!
To get the numeric value of each date, you have to use the DATEVALUE function along with the REPLACE function to remove days string DDD because it will handicap the DATEVALUE function!
This is what you need:
=DAYS(DATEVALUE(REPLACE(B2,1,3,"")),DATEVALUE(REPLACE(A2,1,3,"")))
Please copy it into cell C2 and drag it down.
Hope that helps
Oct 08 2018 11:09 AM
Hello Haytham,
Excellent! That worked.
Thank you so much for your timely help.
Oct 08 2018 10:59 AM
SolutionHi Syed,
This is because that's dates are formatted as texts not as numeric dates!
To get the numeric value of each date, you have to use the DATEVALUE function along with the REPLACE function to remove days string DDD because it will handicap the DATEVALUE function!
This is what you need:
=DAYS(DATEVALUE(REPLACE(B2,1,3,"")),DATEVALUE(REPLACE(A2,1,3,"")))
Please copy it into cell C2 and drag it down.
Hope that helps