Hello guys, I'm agregating data that I'm pulling out from different sheets with different time update. I have no way to change the time for the other sheets (I won't go into details it's complicated).
So I was wondering if there is a formula that can sumifs based on the time: ie if time before 2pm sum data from yesterday, and after 2 pm include data from today?
sumifs()
may do what you want
A sample sheet would help here
The forum does allow for spreadsheets to be uploaded direct to the forum -
Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
Here are the instructions, found at the top of the page again
Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thank youIt's a google sheet so I don't have any attachement but a link. When I try to share it I get this error message "You are not allowed to post any kinds of links, images or videos until you post a few times."
Here is attached the excel versionGoogle Sheet based on time formula.xlsx
i'm not sure what you are after - no expected results on the sheet
also on the cost sheet
no times
But with excel - functions() nothign changes unless an event happens - so for example - if you use now() as you have then that will not change until somethings happens on the spreadsheet
cell change - calculating etc
if you want a live thing going on - then it will need vba - to update the sheet automatically not involving anyone touching the sheet - i dont supply VBA solutions here
to make a long story short: I would like to be able to sumif from the begining of the month up until yesterday if we are after 2 pm but from the begining of the month up until 2 DAYS AGO if we are before 2 pm. Do you think it is possible with a Google formula?
sorry , should have seen googlesheets - will need to perhaps look at that
an IF with
=(NOW()-INT(NOW()))>TIMEVALUE("14:00")
that will give TRUE or FALSE
then you can do the SUMIFS()
todays ago
=SUMIFS(C:C,A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),A:A,"<"&DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-2))
before today
=SUMIFS(C:C,A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),A:A,"<="&DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))
so
=IF((NOW()-INT(NOW()))>TIMEVALUE("14:00"),SUMIFS(C:C,A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),A:A,"<="&DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-2)),SUMIFS(C:C,A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),A:A,"<="&DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))))
but as i say it wont work - until the spreadsheet is updated
i have important into google sheets and seems to give same result
Last edited by etaf; 03-13-2023 at 09:57 AM.
Thank you very much for having looked into my request
you are welcome
I have imported into google sheets
just see if number changes after 14:00
tested and seems to work OK
see cell H5
https://docs.google.com/spreadsheets...it?usp=sharing
should be open to anyone
Last edited by etaf; 03-13-2023 at 10:07 AM.
Will do ASAP. I'll let you know how it goes
it's working this is brilliant!!!!!!!! Thank you so very much
you are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks