Dear all,
I am trying to calculating time period between two dates but some cells have different format which are causing error,
Please help to solve this,
sample data is attached.
imran
Dear all,
I am trying to calculating time period between two dates but some cells have different format which are causing error,
Please help to solve this,
sample data is attached.
imran
Not sure why you are using TEXT() for this, you could just use...
=C2-A2
then format as TIME
By using TEXT, even if it worked for all those rows, you would have a text answer, and even if you wanted to, you would not be able to sum them
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Dear ford,
It's ok but what about the errors in result how to solve this,
Because I have a large data how to convert this to required format,
Regards,
imran.
Dear all,
If someone have different formula to solve this please tell me,
regards,
imran.
If you use the formula as I suggested, there are no errors
Please check the attached file error is available,
regards,
Not all in column A is real date.
I used the formula in column E and see no errors?
A B C D E 1 OPEN DATE RECEIPT DATECLOSE DATE Time period Use this 2 12/7/2016 9:37 12/7/2016 9:39 12/7/2016 9:390:02 0:02 3 12/7/2016 9:33 12/7/2016 9:36 12/7/2016 9:370:03 0:03 4 12/7/2016 7:51 12/7/2016 7:52 12/7/2016 7:530:01 0:01 5 7/13/2016 9:48:47 7/13/2016 9:49:27 AM7/13/2016 9:49:40 #VALUE! 0:00 6 12/7/2016 9:54 12/7/2016 10:08 12/7/2016 10:090:14 0:14 7 7/13/2016 11:08:27 AM 7/13/2016 11:15:03 AM7/13/2016 11:15:37 AM #VALUE! 0:07 8 7/13/2016 11:41:45 AM 7/13/2016 11:45:33 AM7/13/2016 11:46:10 AM #VALUE! 0:04 9 7/13/2016 11:32:25 AM 7/13/2016 11:35:54 AM7/13/2016 11:36:21 AM #VALUE! 0:03
If you format E5 differently, you will see that it actually contains 0:00:53, but with that formatting, it just shows 0
Last edited by FDibbins; 07-17-2016 at 11:55 PM.
Thanks for your support ford,
But I am sorry to say that here in my computer error is still available, may be the problem is in excel,
Any how thanks once again for you nice coperation,
Imran
Can you show me what your formula is, and maybe upload a sample workbook showing the error?
Please check the attached image file,
Regards,
Sorry, can you upload a sample workbook, it's hard to tell from a pic what you have
Please check the attached worksheet on which formula is showing error,
regards,
Rows 2:5 are numbers
Rows 6:8 is text
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
popipipo is correct about the text entries, use this instead...
=IF(ISNUMBER(A2),C2-A2,(DATEVALUE(C2)+TIMEVALUE(C2))-(DATEVALUE(A2)+TIMEVALUE(A2)))
Dear ford,
I am sorry to say same problem exist,
Regards,
imran.
Excel has difficulties with importing times from other sources.
I think you mean 12 july and 13 july07-12-2016 09:37:36
7/13/2016 11:08:27 AM
Excel see the first as 12 december and translate the / as -
Import the original file as a textfile then you can say this column is a date column with the correct size
Thanks Williem,
I will extract it again, I know this problem rise due to import data from other source,,
Thanks once again,
imran.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks