Week Ending Formula

[RhysBillOfficial]

Hi All,

I'm trying to put week ending formula on my excel file but I couldn't figure out why it is not showing the way I want to show.

I want the formula to display, WE 24/08/2020. But on the formula I've used, it shows WE 44067.

Here's the formula I've used,

="WE "&((1-WEEKDAY(A2,2))+A2)
="WE "&IF(WEEKDAY(A3)<=1,A3+1-WEEKDAY(A3),A3+2-WEEKDAY(A3))

I've tried going into Format Cells and tried to adjust the date but no avail.

A help would be appreciated on this one.

Cheers,
@RhysBillOfficial

 

[CA_Punit]

="WE "&TEXT(((1-WEEKDAY(A2,2))+A2),"mm/dd/yyyy")

 

[Joe4]

Welcome to the Board!

That is because dates are really stored as Integers, specifically the number of days since 1/0/1900.
So dates in Excel are really just numbers with special date formats.
To get the date format in your formula, use the "TEXT" function on it, like CA_Punit shows above.

 

[RhysBillOfficial]

That actually works! Thanks a lot.

 

[RhysBillOfficial]

Welcome to the Board!

That is because dates are really stored as Integers, specifically the number of days since 1/0/1900.
So dates in Excel are really just numbers with special date formats.
To get the date format in your formula, use the "TEXT" function on it, like CA_Punit shows above.

didn't thought about the TEXT. haha. thanks a lot though

="WE "&TEXT(((1-WEEKDAY(A2,2))+A2),"mm/dd/yyyy")

That actually works! Thanks a lot.