+ Reply to Thread
Results 1 to 3 of 3

Excel - Distinguish Time category and calculate each portion of time?

Hybrid View

  1. #1
    Registered User
    Join Date
    05-13-2015
    Location
    Canada
    MS-Off Ver
    ?
    Posts
    1

    Excel - Distinguish Time category and calculate each portion of time?

    Hello to all experts, I am encountering some "time" function problem, and will be greatly appreciated if someone can help me with it

    IN ASSUMPTION OF:
    A1 - starting time
    B1 - ending time

    What is the right equation to use to
    1) Categorize the time zone into two groups (? cluster? stratums?)
    1.1) 7:00am ~7:00pm = morning
    1.2) 7:01pm~ 6:59am = night

    2) Capable of concluding from A1 & B1 :
    2.1) Total Hours (this is solved)
    2.2) Within the given time period, how many hours are in "morning"
    2.3) Within the given time period, how many hours are in "night"

    =====================

    For example,
    Leo works from 7 AM (A1) to 10 PM(B1)

    the system is capable of recognizing :
    1) Leo has worked 15 hours
    1.1) 12 hours in morning <<as 7am ~ 7pm = morning>>
    1.2) 3 hours at night <<as 7:01pm ~ 6:59am = night>>


    Thank you!!

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,915

    Re: Excel - Distinguish Time category and calculate each portion of time?

    please attach a sample excel file with expected result
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,657

    Re: Excel - Distinguish Time category and calculate each portion of time?

    As Siva pointed - it would be better to work on sample file with expected results. You may try (separate this into pieces if you need separate columns with output):
    Formula: copy to clipboard
    ="Leo worked: " &TEXT(MOD(B1+17/24,1)-MOD(A1+17/24,1),"hh:mm")&" hour(s) total, ot which: " & TEXT(MAX(MIN(MOD(B1+17/24,1),0.5)-MOD(A1+17/24,1),0),"hh:mm")&" morning, and " & TEXT(MAX(0,MOD(B1+17/24,1)-MAX(MOD(A1+17/24,1),0.5)),"hh:mm") & " night."


    if there is no situation that Leo starts late night (say 5:00 AM and ends in the morning (9:00 AM) shall work fine
    Best Regards,

    Kaper

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 04-25-2020, 03:23 AM
  2. Excel Formula to Calculate Time Span Between Different Time Range.
    By omershafiq2012 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-06-2015, 08:28 AM
  3. Formulae required to distinguish between start and end time.
    By vince hallam in forum Excel General
    Replies: 7
    Last Post: 03-05-2015, 06:36 PM
  4. comparing the time portion of a date/time
    By syphlix in forum Excel General
    Replies: 8
    Last Post: 03-25-2011, 04:08 AM
  5. HOW DO I CALCULATE TIME IN A TIME SHEET FOR EXCEL
    By RAFAEL in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-26-2005, 07:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1