Problem with date

%3CLINGO-SUB%20id%3D%22lingo-sub-2207227%22%20slang%3D%22en-US%22%3EProblem%20with%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2207227%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22excel%20problem.png%22%20style%3D%22width%3A%20687px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F263363iBA01920EF5FA75D9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22excel%20problem.png%22%20alt%3D%22excel%20problem.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2207227%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%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2207302%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2207302%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F996191%22%20target%3D%22_blank%22%3E%40UserExcel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20date%20like%201%2F1%2F2021%20could%20be%20received%20by%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DDATE(C2%2CMONTH(A2%26amp%3BB2)%2CA2)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EDidn't%20catch%20how%20that's%20linked%20to%20monthly%20dates%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2208271%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2208271%22%20slang%3D%22en-US%22%3EThank%20you%20Sergei%20!!%20It%20worked%20!!!!%20I%20just%20needed%20to%20create%20a%20arbitrary%20date%20keeping%20the%20month%20and%20year%20same%20%2C%2C%20Its%20for%20the%20purpose%20to%20use%20time%20intelligence%20function%20in%20power%20bi.%3C%2FLINGO-BODY%3E
Occasional Contributor
7 Replies

@UserExcel 

The date like 1/1/2021 could be received by

=DATE(C2,MONTH(A2&B2),A2)

Didn't catch how that's linked to monthly dates column.

Thank you Sergei !! It worked !!!! I just needed to create a arbitrary date keeping the month and year same ,, Its for the purpose to use time intelligence function in power bi.

@UserExcel , glad it helped

As a comment, usual practice now is to use EOMONTH instead of MonthKey in Date / Calendar table, not first of month. But that's up to you.

Thank you Serge, I have only month and year in my data so I need only date which was missing to use time intelligence ,, my boss is concerned with the monthly and quarterly visual report,, not in daily basis ,,,,

Hi Sergie ,, I have the value of 3 as a month number and 2021 as a year ,, I would like to get a 3/3/2021,, Can you help me ??
Like


A B C
Year Month Date
2 2021 3 3/3/2021

How do i get the date from only month and year like above ??

@UserExcel 

That's the same DATE() function. If third day of the month is hardcoded, when

=DATE( A2, B2, 3)

@UserExcel 

Latest question is in separate thread here Date - Microsoft Tech Community