difference between 2 dates

[Shamz41]

Hi
I have 2 dates in 2 cell A1 and A2 in the following format mm/dd/yyyy, hh:mmpm. can i know how would i be able to calculate the number of days difference bet these 2 dates.

[FDibbins]

Try this...
=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"

If you only want the number of days, then just subtract the later date from the earlier 1, and format as general

[Shamz41]

Hi Ford, This formula showed an error value till I removed the time. the date i have is in the follwing format

Please Login or Register  to view this content.

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

FYI: you are more likely to get a quicker response if you post a file so respondents don't have to "guess" how the data is organised and/or formatted.

[Shamz41]

Hi I have inserted a test file. I would want to count the number of days in the following format

[JohnTopley]

As your "dates" are not in Excel date format ...

Try

=DATEVALUE(LEFT(A2,10))-DATEVALUE(LEFT(B2,10))+1

[Khalidngo]

Hi @Shamz41,

If you always have comma before times in your cell, you can try this too:

=(REPLACE(A2,FIND(",",A2),99,"")+0)-REPLACE(B2,FIND(",",B2),99,"")+1

Regards,

[FDibbins]

Hi Ford, This formula showed an error value till I removed the time. the date i have is in the follwing format

Please Login or Register  to view this content.

oops forgot about the time part. put the cell refs inside INT(), or use helper column to remove the time with INT, then base the calc on that

[Shamz41]

Thanks both formulas worked perfectly

[FDibbins]

Happy to help and thanks for the feedback