Re: $$Excel-Macros$$ Need some help to change data format - urgent
Thanks Vabz. It was indeed very helpful and saved my time. Can i ask one more thing (i m getting a bit greedy!!!) In the sample file attached the list of employees is small and also dates. is there a formula or way where the employee code and then dates would automatically get pulled in (because the original data has too many employees and spread across 6 months i.e.180 days. So plotting the dates manually by each employee and then using this formula will consume significant time as well. Pls suggest. Rgds, Vikas Agarwal On Tuesday, April 29, 2014 5:28:05 PM UTC+5:30, Vabz wrote: > > Hi > > You can do with formula, check yellow colored area. > > Thx > > > On Mon, Apr 28, 2014 at 10:40 PM, Vikas Agarwal > > > wrote: > >> Hi All, >> >> Need some help on excel calculations: >> >> I have a data of employee attendance (daily in/out time). The extract >> from timekeeping software does come in format for me to analyse it further, >> >> Sample file of base data and output required is attached. Request you to >> pls provide me a solution so that this can be done within seconds/minutes. >> >> >> Rgds, >> >> Vikas >> >> 9820427939 >> >> -- >> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES >> >> 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) Jobs posting is not allowed. >> 6) Sharing copyrighted material and their links is not allowed. >> >> NOTE : Don't ever post confidential data in a workbook. Forum owners and >> members are not responsible for any loss. >> --- >> You received this message because you are subscribed to the Google Groups >> "MS EXCEL AND VBA MACROS" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to excel-macros...@googlegroups.com . >> To post to this group, send email to excel-...@googlegroups.com >> . >> Visit this group at http://groups.google.com/group/excel-macros. >> For more options, visit https://groups.google.com/d/optout. >> > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Need some help to change data format - urgent
Hi You can do with formula, check yellow colored area. Thx On Mon, Apr 28, 2014 at 10:40 PM, Vikas Agarwal wrote: > Hi All, > > Need some help on excel calculations: > > I have a data of employee attendance (daily in/out time). The extract from > timekeeping software does come in format for me to analyse it further, > > Sample file of base data and output required is attached. Request you to > pls provide me a solution so that this can be done within seconds/minutes. > > > Rgds, > > Vikas > > 9820427939 > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 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) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Date format - in time out time.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Need some help to change data format - urgent
So... you're asking one or more of us to write the VBA code for you? are you familiar with VBA? what have you done so far? Is the "base data" exactly how it comes from your timekeeping system? the "times" are in a mixed format. some are like: 23:04 (hours, minutes) where others are like: 6.35 Is that 6.35 hours (6:21) or 6 hours and 35 minutes? Do you understand how Excel handles date/time? (the fact that they are numbers, April 29, 2014 at 7:35am is 41,758.31606 days since midnight of 1/1/1900) So, basically, if you combine the date and time for in/out and subtract the two, you get the fraction of a day, multiplied by 24 hrs/day and 60 minutes/hour you get the number of minutes between the two dates. However, some times logged for a specific day are actually on the day BEFORE: the 23:04 entry for 1/1/2014 is ACTUALLY 12/31/2013 at 23:04. So, you need a "rule" that says that in-times after... 18:00? will be calculated from the previous day. Also, it looks like your workday is calculated based on 7 hours (451 is 420 minutes + 31 "extra" minutes) Is this correct? What you ask can be done, but assumptions have to be made. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - > > From: Vikas Agarwal >To: excel-macros@googlegroups.com >Sent: Monday, April 28, 2014 1:10 PM >Subject: $$Excel-Macros$$ Need some help to change data format - urgent > > > >Hi All, > >Need some help on excel calculations: > >I have a data of employee attendance (daily in/out time). The extract from >timekeeping software does come in format for me to analyse it further, > >Sample file of base data and output required is attached. Request you to pls >provide me a solution so that this can be done within seconds/minutes. > > > >Rgds, > > >Vikas > >9820427939 -- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >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) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros. >For more options, visit https://groups.google.com/d/optout. > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Need some help to change data format - urgent
Hi All, Need some help on excel calculations: I have a data of employee attendance (daily in/out time). The extract from timekeeping software does come in format for me to analyse it further, Sample file of base data and output required is attached. Request you to pls provide me a solution so that this can be done within seconds/minutes. Rgds, Vikas 9820427939 -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. in time out time.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
