Hi,
This starts in A1. The formula is in C2 and is shown below the two
Date/time entries.
Date Time
Date Time Formula
So it takes two lines to record
Start
End
6/18/2010 8:00 PM
6/19/2010 2:30 AM 6.50
=IF((+B2-B1)<0,(24-(+(+B1-TIME(2400,0,0))*24)),0)+IF((+B2-B1)<0,((+B2-
TIME(2400,0,0))*24),((+B2-B1)*24))
Hope this helps.
David
On Jun 16, 11:06 pm, gurumurthy sastry Kondury
<gurumurthy.sas...@gmail.com> wrote:
> Hi dave,
>
> In the same way If I want to know the exact number of working hours between
> 19:45 PM yesterday and 16:50PM today ( Considering the user is working in
> second shift from 15:00 PM to 00:00 AM ).
>
> Thanks & Regards,
>
>
>
> On Thu, Jun 17, 2010 at 7:33 AM, who <dlanm...@att.net> wrote:
> > Hi,
> > Below starts in Cell A1, formulas are below. Hope it helps.
>
> > 20500:00 20500 00
> > =LEFT(A1,FIND(":",A1)-1) =RIGHT(A1,2)
> > 18456:56 18456 56
> > =LEFT(A3,FIND(":",A3)-1) =RIGHT(A3,2)
> > 2043 4 2043:4
> > =IF(C1<C3,B1-1-B3,B1-B3) =IF(C1<C3,60-C3,C1-C3) =B5&":"&C5
>
> > Thanks,
> > David
>
> > On Jun 9, 3:27 pm, nickp <nmh...@gmail.com> wrote:
> > > I am trying to subtract large hours and minutes from each other,
> > > unfortunately excel keeps returning a value error.
> > > e.g 20500:00 - 18456:56 = 2043:04.
> > > I have formatted the cells [h]:mm and the calculation works fine with
> > > hours containing only four figures. I would be greatful if any one
> > > could help. Many Thanks
>
> > --
>
> > ----------------------------------------------------------------------------------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 7000 subscribers worldwide and receive many nice notes about
> > the learning and support from the group.Let friends and co-workers know they
> > can subscribe to group at
> >http://groups.google.com/group/excel-macros/subscribe- Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
