Week Ending Formula

RhysBillOfficial

New Member
Joined
Aug 25, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
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
 

Attachments

  • excel.PNG
    excel.PNG
    11.1 KB · Views: 3

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 25, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,462
Members
414,451
Latest member
jrose7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top