+ Reply to Thread
Results 1 to 7 of 7

Date and TIme based on user input

  1. #1
    Registered User
    Join Date
    11-11-2020
    Location
    Florida
    MS-Off Ver
    2016
    Posts
    4

    Date and TIme based on user input

    I pull data fine now with this formula, but limited to only pulling from 0:00-23:00 on one day, what do I need to be able to pull data between days, like 0400-0400?

    =SUMIFS('Total Scans Data2'!$M$5:$M$724,'Total Scans Data2'!K5:K724,Z4,'Total Scans Data2'!$L$5:$L$724,">="&Z6,'Total Scans Data2'!$L$5:$L$724,"<"&TIME(HOUR(Z7),MINUTE(Z7)+15,0))

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Date and TIme based on user input

    We can't see what your data looks like, so it is difficult to advise fully. The yellow banner at the top of the screen gives details of how to attach a sample Excel workbook to one of your posts, and it would help if you did this.

    Pete

  3. #3
    Registered User
    Join Date
    11-11-2020
    Location
    Florida
    MS-Off Ver
    2016
    Posts
    4

    Re: Date and TIme based on user input

    did that work, first time poster...
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Date and TIme based on user input

    The formula in I4 refers specifically to TODAY(), and the formula in N4 refers to O1 which contains a reference to some other file (which we don't have) - I presume it is a date.

    Consequently, both formula are looking for a particular date in column F or column K.

    If you wanted to look for a time range of, say, 04:00 to 03:59 the next day, then I suggest you add the dates and times in one column, rather than have them in separate columns.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    11-11-2020
    Location
    Florida
    MS-Off Ver
    2016
    Posts
    4

    Re: Date and TIme based on user input

    The month/day/time are in drop down lists, the day/month are combined to reflect in the formula for that given day, along with the time to search for. The workbook is rather large, I updated with everything that that one cell would need to reflect the inputs from the drop down list.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-11-2020
    Location
    Florida
    MS-Off Ver
    2016
    Posts
    4

    Re: Date and TIme based on user input

    Anyone else have an idea where I can go from here?

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Date and TIme based on user input

    You could put this formula in cell I5 to combine the date and time together:

    =INT(F5)+MOD(G5,1)

    Apply a custom format of dd-mmm hh:mm if you like, and then copy down to the bottom of your data.

    Then the formula in I4 would become:

    =SUMIFS($H$5:$H$567,$I$5:$I$567,">="&TODAY()-3+$G$1,$I$5:$I$567,"<"&TODAY()-3+TIME(HOUR($H$1),MINUTE($H$1)+15,0)+(TIME(HOUR($H$1),MINUTE($H$1)+15,0)<=$G$1))

    You can ignore the -3 (in red) - I just put that in to change the reference date to suit the dates in your file.

    Note that this adds one day onto the reference date for the end-time, if the end-time is less than or equal to the start-time.

    You can make similar changes to column N.

    Hope this helps.

    Pete

+ 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. Add time to cell value based on user input
    By Justair07 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-03-2016, 02:36 PM
  2. Replies: 3
    Last Post: 06-23-2015, 09:56 AM
  3. Calculate End Date & Time Based on User Input
    By sdawson83 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-23-2015, 06:36 AM
  4. Help with Finding Date based on User Input
    By micmcgee in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-08-2013, 05:22 PM
  5. [SOLVED] Project Timeline with Dynamic Date Range Based on User Input
    By Rollinstone12 in forum Excel General
    Replies: 2
    Last Post: 10-28-2012, 10:07 AM
  6. Date & time auto generated based on input date & time
    By BlastRanger in forum Excel General
    Replies: 18
    Last Post: 09-08-2010, 03:54 AM
  7. Apply Auto Filter Based on Date Input by User
    By ELDAN in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-10-2010, 09:07 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