Hi ALL,
I need to find the differnce between two date fileds and the result column should be in Days:Hrs. The format of the fields are
Fields A1, A2 format are ::: DD/MM/YYYY, HH:MM AM/PM
The result column A3(A2-A1) should be Days:Hrs..
Thanks in Advance !!!
Last edited by simi; 09-18-2009 at 05:24 AM.
Hi,
Try this:=INT(A2-A1)&":"&ROUND(((A2-A1)-INT(A2-A1))*24,3)
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
Re: Find the Difference between date and time
Hi sweep
This formula works fine , the problem is it is not working for my format...PHP Code:=INT(A2-A1)&":"&ROUND(((A2-A1)-INT(A2-A1))*24,3)
Pls refer the attachement BO.xls
Try this formula in C2 copied down to get elapsed time in the format d:hh:mm
=INT(SUBSTITUTE(B2,","," ")-SUBSTITUTE(A2,","," "))&TEXT(SUBSTITUTE(B2,","," ")-SUBSTITUTE(A2,","," "),":hh:mm")
...or you can use just this formula
=SUBSTITUTE(B2,","," ")-SUBSTITUTE(A2,","," ")
and format result cell as d:hh:mm but note that this can't display durations 32 days or more
Last edited by daddylonglegs; 09-18-2009 at 06:00 AM.
Thanks for this formula... the problem is my format is in DD/MM/YYYY HH:MMPHP Code:=INT(SUBSTITUTE(B2,","," ")-SUBSTITUTE(A2,","," "))&TEXT(SUBSTITUTE(B2,","," ")-SUBSTITUTE(A2,","," "),":hh:mm")
But the formula take it as MM/DD/YYYY HH:MM....
Pls help me how to swap month and date position
OK,
This will do it
Please Login or Register to view this content.
Hi,
Its working for general dates.but my format is different.
Could you please give the solution on the attached excel sheet?
the dates are there in Sheet1....
The output should be like XX Hrs YY Mins
P.s: X,Y are Numbers
Last edited by simi; 09-18-2009 at 06:24 AM.
The result column A3(A2-A1) should be Days:Hrs..Which format should the outcome have ?The output should be like XX Hrs YY Mins
My suggestion will work if your default date settings are dd/mm/yy If they are mm/dd/yy then try this formula in C2
=DATE(MID(B2,7,4),MID(B2,4,2),LEFT(B2,2))+TRIM(REPLACE(B2,1,FIND(",",B2),""))-DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))-TRIM(REPLACE(A2,1,FIND(",",A2),""))
format as d:hh:mm
As I pointed out above, this won't correctly show any time periods of 32 days or greater, will that be an issue?
Re: Find the Difference between date and time
Hi,
Thank you very much its works fine
Hi,
Again i have aproblem.... for some rows column B date is not available.
in that case its showing #value. i need # value to be replaced by "No Response"
Advance Thanks
It's probably simpler to convert your times first so In C2 try this formula copied to D2
=IF(A2="","",DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))+TRIM(REPLACE(A2,1,FIND(",",A2),"")))
Format these 2 cells in required format, e.g. mm/dd/yyyy hh:mm
Now in E2 you can calculate days and hours and minutes with this formula
=IF(D2="","No Response",INT(D2-C2)&" Days "&TEXT(D2-C2,"hh:mm"))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks