+ Reply to Thread
Results 1 to 6 of 6

track 10 min blocks of time on a calendar based on a database where time in & time left ar

  1. #1
    Registered User
    Join Date
    08-15-2016
    Location
    nashville, USA
    MS-Off Ver
    2010
    Posts
    10

    track 10 min blocks of time on a calendar based on a database where time in & time left ar

    I came up with a formula attempting to confirm (true or false) if 3 logic conditions are met.

    Iím attempting to determine if any person being tracked on my spreadsheet entered the facility and was physically here between the time-period say 11:00 to 11:10 on a specific date. I have a calendar on a different tab where I'm attempting to utilize this formula.

    On the spreadsheet I use to I track peoples comings and goings the tab is labeled ďUsageĒ. Entry to the facility is entered in column H, exit time is entered in column I. and date is entered in column E

    Someone could come in at 10:00; be here until 12 noon and hopefully the formula would still return a value of true because the person was on the tracking sheet as being here on that specific date between the time period of 11:00-11:10. And if all 3 conditions are true the calendar would be formatted to turn that cell (the 11-11:10 Cell for that date) green.

    There have been several attempts at formulas but keep getting Value error.Ē ]

    After some time and thought I realized more descriptive information of the problem may help clarify what I'm attempting to do and what the actual problem is.

    The calendar below is laid out in 10 min. increments from 7am to 16:00.
    I wanted to create a formula that would look at the data in the table and be able to determine if an entry (on the data tracking sheet) in the start time and stop time column included one or more blocks of time (10 minute blocks) for a specific date. If there is a 10 minute block of time identified as having tracked someone being present at the facility on a specific date that cell would be formatted to turn green so that activity in the facility could be easily visually tracked.


    So far Iíve had some assistance from the Mr. Excell forum and a couple of formulas developed from those ideas.

    {=AND(IF(E1000:E1999=DATE(2018,1,17),IF(H1000:H1999)>TIME(11,0,0),-IF(I1000:I1999)0,1))} Returns value of FALSE

    In my mind this formula says (if condition 1(date is 1-17-2018), condition 2(Time In is >11:00) and condition 3(Time out is < 11:20) all 3 conditions are true therefore the resulting value given would be 1. To follow up, the next step would be to format the cell to turn green if the value was 1.

    The whole calendar could be populated with formulas for each individual date and time period to track activity based on data from the tracking sheet

    Several other formulas have been developed but also failed.

    {=AND((Usage!H1000:H1999>=TIME(11,0,0)),(Usage!I1000:I1999<=TIME(11,20,0)),(Usage!E1000:E1999=DATEVALUE("1/17/2018")),0,1)} Return value of FALSE

    So far all my attempts to modify ideas offered in the Mr. Excell forum have failed. What I thought were logical formulas do not work as I thought they would.

    Does anyone out there have any ideas. Any help would be Greatly Appreciated.

    Respectfully,

    Karl E

    The following table is on a tab labled Usage:

    E F G H I J
    Date LibraryResource VLER TimeIn TimeOut E/T

    1128 1/12/2018 Employee Computer 10:30 11:00 0:30:00
    1129 1/12/2018 MHeV Assist y 12:00 12:10 0:10:00
    1130 1/12/2018 internet 8:30 10:30 2:00:00
    1131 1/12/2018 internet 8:30 9:30 1:00:00
    1132 1/12/2018 MHeV Assist 9:00 9:10 0:10:00
    1133 1/12/2018 E-Benefits Assist 10:00 10:20 0:20:00
    1134 1/17/2018 internet 11:01 11:19 0:18:00
    1135 1/17/2018 internet 11:00 11:30 0:30:00
    1136 1/17/2018 Employee Computer 10:30 11:30 1:00:00
    1137 1/17/2018 Employee Computer 10:30 11:30 1:00:00
    1138 1/17/2018 MHeV Assist 10:00 10:10 0:10:00
    1139 1/17/2018 MHeV Assist 10:00 10:05 0:05:00
    1140 1/17/2018 MHeV Assist 10:00 10:15 0:15:00
    1141 1/17/2018 MHeV Assist 10:15 10:30 0:15:00
    1142 1/17/2018 MHeV Assist 10:30 10:35 0:05:00
    1143 1/18/2018 Internet Personal Device 9:00 10:00 1:00:00
    1144 1/18/2018 Employee Computer 9:30 10:00 0:30:00
    1145 1/18/2018 internet 9:00 10:00 1:00:00



    C D E F G H I J K L M N O P Q R S T U V AF AG AH
    January
    01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 29 30 31
    7:30
    7:40
    10:00
    10:10
    10:20
    10:30
    10:40
    10:50
    11:00
    11:10
    11:20
    11:30
    11:40
    11:50
    12:00
    16:00

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: track 10 min blocks of time on a calendar based on a database where time in & time lef

    An uploaded Excel file representative of what you are working with will be helpful.

    If you are not familiar with how to do this:

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  3. #3
    Registered User
    Join Date
    08-15-2016
    Location
    nashville, USA
    MS-Off Ver
    2010
    Posts
    10

    Re: track 10 min blocks of time on a calendar based on a database where time in & time lef

    Thank you for your help Sir!
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,824

    Re: track 10 min blocks of time on a calendar based on a database where time in & time lef

    Is this what you wanted??? In D3, copied across and down:

    =SUMPRODUCT((Usage!$B$1000:$B$1150=Calendar!D$2)*(Usage!$D$1000:$D$1150<=Calendar!$C3)*(Usage!$E$1000:$E$1150>=Calendar!$C3+10/1440))>0
    Attached Files Attached Files
    Glenn



  5. #5
    Registered User
    Join Date
    08-15-2016
    Location
    nashville, USA
    MS-Off Ver
    2010
    Posts
    10

    Re: track 10 min blocks of time on a calendar based on a database where time in & time lef

    Yes it is very close to what I'm looking for. Your awesome!! I had to make a small modification
    =SUMPRODUCT((Usage!$B$1000:$B$1150=Calendar!D$2)*(Usage!$D$1000:$D$1150<=Calendar!$C3)*(Usage!$E$1000:$E$1150>=Calendar!$C3+10/1440))>0 C3 had to be changed to C4
    and the rest of the formulas had to be adjusted in the same way (to increase the value of the highlighted C by 1.

    With the modification the formula looks logical and correct (at least to me); but some of the results didnít give the result I thought they should.

    If we were to zero in on a small portion of this I think I could communicate better. If we just look at Jan 17th start and end dates on the database and 7:30-13:00 on the calendar.

    Looking at the raw data on the database I see 10:00 to 10:10 and cumulatively the time between 10:20 to 11:30 should render a result of True. On the Calendar C18 and C20-C27 should
    show to be true (at least as I read the formula). The actual results say otherwise (so Iím obviously not interpreting what this formula is doing correctly).
    The formula results in C21-C25 to be True (as I thought is should) but it rendered C26 & C27 to be False. I donít follow this at all and cant see how that formula
    would produce a False result in those two fields.
    I also donít see how its not recognizing C18 or C20 as True.

    What youíve come up with has gotten me closer than anybody. Iím truly grateful. Would you please take another look at this with this new information. Thank you for any help you provide.

    Sincerely, Karl E
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,276

    Re: track 10 min blocks of time on a calendar based on a database where time in & time lef

    If you run the Evaluate Formula feature on cell T18 you see that the 10:00 AM values from the Usage sheet all round to 0.416666666666667 while the 10:00 AM in cell C18 is 0.416666666666666. I believe that this is due to column C (Calendar sheet) being populated by the formula.
    If you instead:
    1) type 7:40 into cell C4,
    2) Select cells C3:C4,
    3) And drag the fill handle down the value in T18 becomes TRUE.
    Selecting the portion of the formula that reads Calendar!$C18 and pressing the F9 key, you see that cell also now round to 0.416666666666667
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] How to calculate Time Left (remaining time) using percentage
    By alexgoaga in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-20-2017, 10:55 AM
  2. [SOLVED] time sheet help, format TIME and formula based on the actual time entered.
    By bh mng in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-06-2016, 01:10 PM
  3. Replies: 0
    Last Post: 03-04-2016, 06:38 PM
  4. Replies: 10
    Last Post: 11-08-2014, 04:51 PM
  5. Replies: 5
    Last Post: 04-18-2013, 11:31 AM
  6. Tool/template to track available time on calendar
    By Rubygirl in forum Excel General
    Replies: 2
    Last Post: 05-08-2006, 07:30 PM
  7. Calculating days & time left from start date/time to end date/time
    By marie in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-07-2005, 10:40 AM

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06459 seconds
  • Memory Usage 9,068KB
  • Queries Executed 16 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (6)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (3)postbit_attachment
  • (6)postbit_legacy
  • (6)postbit_onlinestatus
  • (6)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1