Hi Deba,
For the Login Time column, the reason SUM is not working properly is that many of the login times are being interpreted as text instead of as [h]:mm:ss entries. 1) For the login time column with a custom format: [h]:mm:ss or the corresponding preset "Time" format: 37:30:55 2) Then, only enter valid times. Your problem entries all have minutes or seconds > 59, and Excel knows that this could not indicate time properly, so treats it as text. Correct the following entries: 879:55:64 899:54:78 98:54:87 87:54:78 198:89:55 3) Optionally remove cell justification. You have the cells left-justifed. If you remove justification, correct times will be right-justified, and incorrect ones will be interpreted as text and therefore left-justified. This helps in noticing a problem early on. 4) Get rid of the TEXT function in your total formula (the cell format takes care of formatting), and simply use: =SUM(B2:B8) For the Total login cell in the summary area (F3), simply refer to the total in B9 directly: =B9 For the No of days cell (F4): a. If you need the number of 24-hour periods for the Total login time use: =B9 and format the cell as a number (it will default to time format). The result will be in terms of days, since that is how Excel stores dates and times. b. If you need the number of periods of length "1 day hour" (the number in F1), use: =B9*24/F1 (and format the cell as a number) For the Login time for 1 day. cell (F5), I don't really understand the formula or what you want, but perhaps the formula is correct and you just need to format the result for time: [h]:mm:ss Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Deba Ranjan Sent: Saturday, June 02, 2012 12:09 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Date format calculation Dear Experts, i have again got confusion in date and time format calculation, Please see the attached file, i have elaborate the details in the sheet. Please help in calculating the problem. Thanks & Regards, Deba Ranjan P -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
