+ Reply to Thread
Results 1 to 7 of 7

Sum If when using data with a timestamp

  1. 07-15-2023, 06:20 AM #1
    gemmac
    gemmac is offline
    Registered User
    Join Date
    02-10-2023
    Location
    Newcastle UK
    MS-Off Ver
    Excel 365
    Posts
    20

    Sum If when using data with a timestamp

    Hi

    I have a sheet set up and I am having a bit of trouble setting up a formula. Please find it attached.

    I am wanting formulas so that if it is a specific date and the timestamp was before 11:00 I want the sum of the P/L column.

    Similarly i want the same for when the timestamp is 11:00 onwards.

    Thanks in advance
    Gemma
    Attached Files Attached Files
    Last edited by gemmac; 07-15-2023 at 06:47 AM.
    Reply With QuoteRegister To Reply
  2. 07-15-2023, 06:32 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,978

    Re: Sum If when using data with a timestamp

    Please update your profile to Excel 365.

    I am not sure that Excel formulae can read the timestamp, so this might require VBA.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-15-2023, 06:46 AM #3
    gemmac
    gemmac is offline
    Registered User
    Join Date
    02-10-2023
    Location
    Newcastle UK
    MS-Off Ver
    Excel 365
    Posts
    20

    Re: Sum If when using data with a timestamp

    thanks for reply. ive got round it by adding another column , putting a 1 in manually for anything before 11am and a 2 for anything after.

    profile update.

    Thanks again
    gemma
    Reply With QuoteRegister To Reply
  4. 07-15-2023, 06:47 AM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,978

    Re: Sum If when using data with a timestamp

    OK - thanks. Please mark the thread as solved.
    Reply With QuoteRegister To Reply
  5. 07-15-2023, 07:10 AM #5
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,783

    Re: Sum If when using data with a timestamp

    H2=IF($G2<>"",SUMPRODUCT((Table2[Date]=G2)*(MOD(Table2[TimeStamp],1)<=RIGHT($H$1,5)+0)*(Table2[P/L])),"")

    Copy down


    I2=IF(($G2<>"",SUMPRODUCT((Table2[Date]=G2)*(MOD(Table2[TimeStamp],1)>=RIGHT($H$1,5)+0)*(Table2[P/L])),"")

    copy down
    Reply With QuoteRegister To Reply
  6. 07-15-2023, 07:49 AM #6
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,978

    Re: Sum If when using data with a timestamp

    @Caracalla

    Both formulae return 0.

    AliGW on MS365 Beta Channel (Windows 11) 64 bit

    G
    H
    I
    1
    		 Date P/L when TimeStamp to 10:59 P/L when Timestamp from 11:00
    2
    01/07/2023
    £0.00
    £0.00
    3
    08/07/2023
    £0.00
    £0.00
    4
    15/07/2023
    £0.00
    £0.00
    Sheet: Sheet1
    Reply With QuoteRegister To Reply
  7. 07-15-2023, 08:21 AM #7
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,783

    Re: Sum If when using data with a timestamp

    file attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. vba to analyze timestamp, make changes to timestamp, run sql query inside for each loop
    By batador in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-19-2022, 01:42 PM
  2. Timestamp macro that cannot be overwritten by another timestamp
    By xgingerkingx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-12-2020, 01:06 PM
  3. Permanent Timestamp for first entry then remain that timestamp even with edits. A Log file
    By dhake in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-09-2016, 02:23 PM
  4. Timestamp data entry and highlight when no data entered after 10 minutes
    By jdcarter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2015, 08:19 AM
  5. timestamp after entry of data
    By scabertrain in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-05-2014, 02:26 PM
  6. [SOLVED] inserting a timestamp on the data sheet when a raw data file is imported
    By macrorookie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-14-2014, 02:18 PM
  7. When a timestamp doesn't behave as a timestamp
    By mredekopp in forum Excel General
    Replies: 3
    Last Post: 03-07-2011, 03:39 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1