• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Time calculation

U

ushaanu

Member
Hi all,

I have some data in excel for time calculation with working hrs 9:00 to 5:30 Monday to Friday like

08:10 - 09:45 =00:45

thanks
anu
 
So?
Cases like from 09:00 to 05:30, it would good to have a 'datepart' too.
Sample: from 04/08/2016 09:00 to 05/08/2016 05:30,
then there are only one night between start and end.
It's possible to use same formula all the time:
End time - Start time = difference
 
thanks for your reply Vletm,

actually my time window starts 9:00 and end 17:30 . so if some one works starts 8:10 to 9:45 than working hrs should be only 00:45 as window starts 09:00

anu
 
ushaanu said:
thanks for your reply Vletm,

actually my time window starts 9:00 and end 17:30 . so if some one works starts 8:10 to 9:45 than working hrs should be only 00:45 as window starts 09:00

anu
Click to expand...
ushaanu said:
Hi all,

I have some data in excel for time calculation with working hrs 9:00 to 5:30 Monday to Friday like

08:10 - 09:45 =00:45

thanks
anu
Click to expand...

Hi,

Please try this:

=IF(Start Time<=9:00AM,(End Time - 9:00 AM)*24,(End Time - Start Time)*24)

Regards,

Ashok Pai
 
Hi ,

You have liked post #4 by Ashok Pai ; does this mean your problem is resolved ?

If not , can you upload a workbook with some ( say around 20 rows ) data in it ?

Narayan
 
Hi ,

Unless you upload a file which has all possible variations in the data , it is difficult to anticipate all the possibilities.

On the uploaded data , this formula works :

=MIN("17:30" , B2) - MAX(A2 , "09:00")

Narayan
 
Last edited:
You must log in or register to reply here.
Back
Top