Home

Modifying formula based on Date and time

%3CLINGO-SUB%20id%3D%22lingo-sub-308313%22%20slang%3D%22en-US%22%3EModifying%20formula%20based%20on%20Date%20and%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308313%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EDear%20All%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EPlease%20modify%26nbsp%3B%20the%20highlighted%20bold%20formula%20that%20gives%20the%20result%20in%20such%20a%20way.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%3DSUM(COUNTIFS(RM!%24F%3A%24F%2C%24D25%2CRM!%24H%3A%24H%2C%7B%22COMP%22%2C%22CLOSE%22%2C%22WCLOSE%22%7D%2C%3C%2FSPAN%3E%3CEM%3E%3CSTRONG%3ERM!%24I%3A%24I%2C%22%26gt%3B%3D%22%26amp%3BDATE(2018%2C12%2C1)%3C%2FSTRONG%3E%3C%2FEM%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSTRONG%3ERM!%24I%3A%24I%2C%22%26lt%3B%3D%22%26amp%3BEOMONTH(DATE(2018%2C12%2C1)%3C%2FSTRONG%3E%3CSPAN%3E%2C0)))%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E1.%26nbsp%3B%3C%2FSPAN%3E%3CEM%3E%3CSTRONG%3ERM!%24I%3A%24I%2C%22%26gt%3B%3D%22%26amp%3BDATE(2018%2C12%2C1)%26nbsp%3B%20It%20will%20calculate%20based%20on%201dec%202018%20time%2000%3A59%3A59.000%20am%20(hh%3Amm%3Ass.mmm)%3CBR%20%2F%3E%26nbsp%3B2.%3C%2FSTRONG%3E%3C%2FEM%3E%3CSTRONG%3ERM!%24I%3A%24I%2C%22%26lt%3B%3D%22%26amp%3BEOMONTH(DATE(2018%2C12%2C1)%26nbsp%3B%3C%2FSTRONG%3E%3CEM%3E%3CSTRONG%3EIt%20will%20calculate%20based%20on%2031dec%202018%20time%2023%3A59%3A59.000%20pm%3CBR%20%2F%3E%3CBR%20%2F%3EPlease%20help%20me%20to%20get%20the%20result%20based%20on%20date%20and%20time%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3CBR%20%2F%3E%3CBR%20%2F%3ERegards%2C%3CBR%20%2F%3Ealigahk06%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-308313%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-308414%22%20slang%3D%22en-US%22%3ERe%3A%20Modifying%20formula%20based%20on%20Date%20and%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308414%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Abdul%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20add%20TIME%20to%20calculations%20like%3C%2FP%3E%0A%3CPRE%3E(DATE(2018%2C12%2C1)%2BTIME(0%2C59%2C59))%0A%0A(EOMONTH(DATE(2018%2C12%2C1)%2C0)%2BTIME(23%2C59%2C59))%0A%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Abdul H Khan
Occasional Contributor

Dear All,
Please modify  the highlighted bold formula that gives the result in such a way.

=SUM(COUNTIFS(RM!$F:$F,$D25,RM!$H:$H,{"COMP","CLOSE","WCLOSE"},RM!$I:$I,">="&DATE(2018,12,1),RM!$I:$I,"<="&EOMONTH(DATE(2018,12,1),0)))

1. RM!$I:$I,">="&DATE(2018,12,1)  It will calculate based on 1dec 2018 time 00:59:59.000 am (hh:mm:ss.mmm)
 2.
RM!$I:$I,"<="&EOMONTH(DATE(2018,12,1) It will calculate based on 31dec 2018 time 23:59:59.000 pm

Please help me to get the result based on date and time 

Thanks

Regards,
aligahk06

1 Reply

Hi Abdul,

 

You may add TIME to calculations like

(DATE(2018,12,1)+TIME(0,59,59))

(EOMONTH(DATE(2018,12,1),0)+TIME(23,59,59))

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies