Dear Paul,
Sorry for the very delayed reply. This is my friend data, I have suggested
him to track the time in correct format.
Thanks a lot for your help.
Regards,
Vijayendra
2014-10-15 22:15 GMT+05:30 Paul Schreiner :
> The problem is with the way the times are entered.
> We have a NUMBER that's being INTERPRETED as time.
> But the same interpretation isn't always true.
>
> In once case, 10.50 is interpreted as 10:50PM
> in another case, 17.00 is interpreted as 5:00am of the next day.
>
> Then there's a start time of 21.00 WHAT IS THAT?
>
>
> in order to handle this, we have to be able to apply rules consistently.
>
> Either we determine where the numbers are coming from (the data source)
> and modify how they are entered, or we have to somehow figure out
> consistent rules.
>
> ---
> If changing the way the numbers are entered is an option, then that would
> be best.
>
> The most logical and consistent way would be to make the cell contents be
> an ACTUAL date/time.
>
> In the line for February 13, the start time can be entered as:
> 2/13/2013 9:00 AM
> You can actually change the display format to:
> h.mm
> and it will DISPLAY as 9.00
> but the actual VALUE is a REAL Excel date/time.
>
> since the end time was the next day,
> the entry would be: 2/14/2013 5:00 AM
>
> ---
>
> If you don't have any control over how the entries are made, then we have
> to work on the rules.
> To be honest, I can't see anything consistent.
>
> If a number in the start time (AM) column is over 12, does that mean it
> was a PM value?
> if a number in the Out time(PM) column is over 12, does that mean it is AM
> of the following day?
>
> I might be able to work with that.
> but for Feb 11, does 21.00 mean that the start time is 9:00pm
> and 16.00 means that the end time is 4:00am of the next day?
>
> We could test the converted "out time" to see if it is less than the "In
> time" and if it is, then add 12 hours to the out time
>
> --
>
> To be honest, all of these formulae are working to accomplish one thing:
> convert a number into a time and perform a calculation.
>
> I don't know if you have any familiarity with VBA macros...
> But attached is a workbook in which I created a Change Event macro.
> You can enter the time as you have been (8.10, 17.20, etc)
> and it will convert the time to a date/time based on the criteria i've
> observed.
>
> Then, it's simple subtraction to get the hours (well, subtraction gives
> you a fraction of DAYS, so you have to multiply it by 24 hours/day)
>
> But you can DISPLAY the value how you choose.
>
> If you don't like it, or you don't want to dive into the world of macros,
> then nevermind.
>
> *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:* Vijayendra Rao
> *To:* excel-macros@googlegroups.com
> *Sent:* Wednesday, October 15, 2014 11:14 AM
>
> *Subject:* Re: $$Excel-Macros$$ Help in Time Tracker
>
> Dear Paul,
>
> Thanks for your formula. It worked everywhere except any day which worked
> more than 12 hours. I have attached excel file after putting your formula.
>
> Few days employee worked more than 12 hours. Eg. on 13rd February employee
> came to office @ 9.00 am and returned next day of @ 5.00 am. As it is next
> day, i have added 12 hours. Is there any other way to rectify this problem.
>
>
> 2014-10-14 17:01 GMT+05:30 Paul Schreiner :
>
> The problem seems to be with morning entries between midnight and 1:00am.
> in the time() function, 12 is noon rather than midnight.
> so the function thinks the time is after noon in stead of after midnight.
>
> To fix that you can subract 12 from the hours.
> If the result is negative, the time() function will produce an error and
> you can try it without subtracting 12:
>
> =IFERROR(TIME(INT(C42)-12,(C42-INT(C42))*100,0),
> IFERROR(TIME(INT(C42),(C42-INT(C42))*100,0),""))
>
> The entries OUT time seems to have some issues.
> Why are some of the "out" times greater than 12?
>
> what does this number represent?
>
>
> *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:* Vijayendra Rao
> *To:* excel-macros@googlegroups.com
> *Sent:* Tuesday, October 14, 2014 3:30 AM
> *Subject:* Re: $$Excel-Macros$$ Help in Time Tracker
>
> Dear Paul,
>
> Thank you very much your help, i have applied your formulas in my sheet
> and it works perfect. I am facing problem in total hours, it is showing
> some wrong figure.
>
> Can you p
