+ Reply to Thread
Results 1 to 6 of 6

Calculate a shift from day and time

  1. #1
    Registered User
    Join Date
    01-22-2023
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    Calculate a shift from day and time

    Hi guys,

    I'm wondering if one of you could help with a problem.

    My work runs 4 shifts over the week, 2 shifts Monday - Friday, 06.30-15.30 / 15.30-01.30, and two shifts on Saturday & Sunday, 05.30-17.30 / 17.30-05.30.

    Our machine data systems produce reports that populates the first five columns shown in the Excel image below, what I'm wanting to do if insert a formula into columa F that will then calculate / lookup which shift that row of data refers to based on the weekday and time. As an example, the first row entry is 09.01 on Monday 16/01/23, so column F should show as 'DAYS'.

    I got as far as writing the formula ''=IF(AND(D2>TIME(6,15,0),D2<=TIME(16,30,0)),"DAYS","BACKS")'' but this won't take into account the weekend shifts?

    Any help greatly appreciated :-)

    Marc

    Capture shift 1.PNG

  2. #2
    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,609

    Re: Calculate a shift from day and time

    Even with such a simple case sample workbook (see yellow banner near the top of the page) is pretty usefull to provide correct suggestions.
    One clarification Sun 05:30:00 sharp (05:29:59 or 05:30:01 are clear :0) is Weekend day or weekend back? The same question for 3:30PM on weekdays
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    01-22-2023
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    Re: Calculate a shift from day and time

    sample.xlsx

    Hi, I've attached a sample of my workbook.

    If nesesary I can change the shift times to finish a minute before the next shift starts, so they would be;

    DAYS 06.30-15.29

    BACKS 15.30-01.29

    WEEKEND DAYS 05.30-17.29

    WEEKEND BACKS 17.30-05.29
    Last edited by marcs74; 01-22-2023 at 08:15 AM.

  4. #4
    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,609

    Re: Calculate a shift from day and time

    It's not the shortest formula I've seen :-) but shall work:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    First if tests whether its weekend or working day then for weekend testing if its fists or second half of "the day shifted by 5:30"
    similar one is done if first result was working day. First we look if it's first 9 hours of a day "shifted by 6:30", then if it's first 10 hours of day shifted by 17:30 and if this condition is not met we are on working day between 1:30 and 6:30 next day

    MOD is used to get rid of negative results of "mooving start of the day from 00:00 by 5:30 or 6:30"
    Attached Files Attached Files
    Last edited by Kaper; 01-22-2023 at 08:30 AM.

  5. #5
    Registered User
    Join Date
    01-22-2023
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    Re: Calculate a shift from day and time

    Quote Originally Posted by Kaper View Post
    It's not the shortest formula I've seen :-) but shall work:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    First if tests whether its weekend or working day then for weekend testing if its fists or second half of "the day shifted by 5:30"
    similar one is done if first result was working day. First we look if it's first 9 hours of a day "shifted by 6:30", then if it's first 10 hours of day shifted by 17:30 and if this condition is not met we are on working day between 1:30 and 6:30 next day

    MOD is used to get rid of negative results of "mooving start of the day from 00:00 by 5:30 or 6:30"
    That is brilliant, you are a legend mate!

    Can't thank you enough :-)

  6. #6
    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,609

    Re: Calculate a shift from day and time

    Glad to see it worked, and thanks for a reputation point and for marking thread SOLVED

+ 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. Calculate Shift Hours (Date/Time difference in an interval)
    By seanpark22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-28-2020, 04:39 PM
  2. Replies: 1
    Last Post: 07-13-2019, 01:12 PM
  3. Replies: 3
    Last Post: 04-04-2019, 10:50 PM
  4. Timeline - Calculate end date/time with shift/lunch start and end
    By m_west510 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-21-2018, 09:05 PM
  5. [SOLVED] Calculate shift according time
    By nafriirfan in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-16-2017, 12:56 AM
  6. Replies: 5
    Last Post: 12-02-2015, 03:09 PM
  7. Replies: 0
    Last Post: 06-14-2014, 02:48 PM

Tags for this Thread

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