Adding number to Text

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,630
Office Version
  1. 2019
Platform
  1. Windows
I have the following text on sheet TB

Trial Balance : 01/09/13 to 31/08/14

I have the following formula on sheet "Notes"


="Balance "&TEXT(MID(TB!A3,17,8),"DD MMM YYYY")+29

The result of the formula is Balance 41548

It should read Balance as at 30 September 2013 as I want to extract the ef of month date from 01/09/2014 (format dd/mm/yyyy)

Your assistance in resolving this is most appreciated
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Canapone

Active Member
Joined
May 10, 2007
Messages
463
Hi,

just made some attempts to check the formula.

="Balance "&TEXT(MID(TB!A3,17,8)+29,"DD MMM YYYY")

Hope it helps
 

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,213
Probably you better of working with formula eomonth (instead of +29) since not all months have the same end date.
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
It's much easier to have cell with a master date in somewhere , maybe a hidden sheet then refer to that cell. I would name the cell masterDate and use that in formulas.

As suggested EOMONTH is the best way for a dynamic date .

="Balance at " &TEXT(EOMONTH(MID(TB!A3,17,8),0),"DD MMM YYYY")

Your formula has the number of days in the wrong place

="Balance "&TEXT(MID(TB!A3,17,8)+29,"DD MMM YYYY")
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,630
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the help much appreciated
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,630
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the help. The EOMONTh formula is great as I do not have to amend each month
 

Watch MrExcel Video

Forum statistics

Threads
1,108,908
Messages
5,525,577
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top