Date and Text in one formula

C

CarolineHedges

I am trying to write a formula which has text and then a date which i
taken from a different sheet in the workbook.

EG the text is "Nav as at 31st March 06"

With 'Nav as at' being the text and the date 31st March being picked u
from the other sheet.

However the date on the other sheet is the following month, ie 30t
June. SO I would need it to pick up the End of the month and minus i
by one month and add it to the text.

I have tried several methods but none seem to work!!

Thanks

Carolin
 
F

Franz Verga

Nel post *CarolineHedges* ha scritto:
I am trying to write a formula which has text and then a date which is
taken from a different sheet in the workbook.

EG the text is "Nav as at 31st March 06"

With 'Nav as at' being the text and the date 31st March being picked
up from the other sheet.

However the date on the other sheet is the following month, ie 30th
June. SO I would need it to pick up the End of the month and minus it
by one month and add it to the text.

I have tried several methods but none seem to work!!

Thanks

Caroline
Click to expand...


Hi Caroline,

maybe (quite sure... ;-) ), you didn't tried this one:

="Nav at "&TEXT(EOMONTH(D3,-1),"dd mmmm yyyy")

where D3 is the cell in which there is the date, so you have to arrange the
reference as you need. Also "dd mmmm yyyy" is the formatting code for the
date, you have to arrange this too as for your need.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
G

Guest

If A1 contains 30/06/2006 then:

="Nav as at " & TEXT(DATE(YEAR(A1),MONTH(A1),0),"dd mmmm yy")

will display "Nav as at 31 May 06"

Does this help?
 
F

Franz Verga

Nel post *Franz Verga* ha scritto:
Nel post
*CarolineHedges* ha scritto:


Hi Caroline,

maybe (quite sure... ;-) ), you didn't tried this one:

="Nav at "&TEXT(EOMONTH(D3,-1),"dd mmmm yyyy")

where D3 is the cell in which there is the date, so you have to
arrange the reference as you need. Also "dd mmmm yyyy" is the
formatting code for the date, you have to arrange this too as for
your need.
Click to expand...

Because the EOMONTH function need the Analysis Tool Pack (ATP) installed,
you may also prefer to change the previous formula with this one:

="Nav at "&TEXT(DATE(YEAR(D3),MONTH(D3),0),"gg mmmm aaaa")

which don't need ATP.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
F

Franz Verga

Nel post *Franz Verga* ha scritto:

[cut
="Nav at "&TEXT(DATE(YEAR(D3),MONTH(D3),0),"gg mmmm aaaa")
Click to expand...

should be intende as:

="Nav at "&TEXT(DATE(YEAR(D3),MONTH(D3),0),"dd mmmm yyyy")

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
A

Abanana

I am trying to crack the code in my work place by making
A rota for staff

Here’s the implication and your help in this one is a huge blessing
please help me here my email

(e-mail address removed)


LD is long day 12 hours
N=night shift 12
E=early shift 8
L=late shift 8
ENP=Emergency nurse practitioner 8
EENP =8
LENP=8

SD=study day 8 hrs

SD needs to be calculated as 8 hrs on total hours row but on column
number of employee in the dept is 0

AL=annual leave 0
BH=bank holiday 0
ML=maternity leave 0
S=off sick 0
Peads=working in paediatric dept 12 on (total hours row) 0 hrs on
column total number of staff because this person is not in adults dept


Off=off duty 0
LDCDU=Long day in clinical decision unit 12
ECDU=8
LCDU=8

Column A

From A4 to A68 the names of the staff

Rows

From b2 to ac2 days mon tue wed etc
From b3 to ac3 dates 1 2 3 etc


From b4 to ac65 shifts inserted i.e LD N E AL S off etc

Rows
From ad4 to ad65 each cell gives the total hours of each employe

Column

A66 cell ealy
To calculate from b4 to b65 how many employee are in the dept from 8 am
on to 8pm

A67 cell Late
to calculate from b4 to b65 how many employee are in the dept from
mid-day to 8pm

A68 night
To calculate from b4 to b65 how many employee are in the dept on night
shift

I hope all the details are easy to understand if not

Please email me should you need any more details

please help

Many many thanks anticipated
 
F

Franz Verga

Nel post *Abanana* ha scritto:
I am trying to crack the code in my work place by making
A rota for staff

Here's the implication and your help in this one is a huge blessing
please help me here my email

(e-mail address removed)


LD is long day 12 hours
N=night shift 12
E=early shift 8
L=late shift 8
ENP=Emergency nurse practitioner 8
EENP =8
LENP=8

SD=study day 8 hrs

SD needs to be calculated as 8 hrs on total hours row but on column
number of employee in the dept is 0

AL=annual leave 0
BH=bank holiday 0
ML=maternity leave 0
S=off sick 0
Peads=working in paediatric dept 12 on (total hours row) 0 hrs on
column total number of staff because this person is not in adults dept


Off=off duty 0
LDCDU=Long day in clinical decision unit 12
ECDU=8
LCDU=8

Column A

From A4 to A68 the names of the staff

Rows

From b2 to ac2 days mon tue wed etc
From b3 to ac3 dates 1 2 3 etc


From b4 to ac65 shifts inserted i.e LD N E AL S off etc

Rows
From ad4 to ad65 each cell gives the total hours of each employe

Column

A66 cell ealy
To calculate from b4 to b65 how many employee are in the dept from 8
am on to 8pm

A67 cell Late
to calculate from b4 to b65 how many employee are in the dept from
mid-day to 8pm

A68 night
To calculate from b4 to b65 how many employee are in the dept on night
shift

I hope all the details are easy to understand if not

Please email me should you need any more details

please help

Many many thanks anticipated
Click to expand...


May it could be better if you could post an example file on www.savefile.com
(remembering to delete all personal/sensitive data).

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
A

Abanana

Thanks a lot Franz

Franz said:
Nel post *Abanana* ha scritto:



May it could be better if you could post an example file on
www.savefile.com
(remembering to delete all personal/sensitive data).

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
Click to expand...
 
C

CarolineHedges

Having a problem re: my 1st email.

I have renamed the module but now the macro is called for eg:
cashflowscashflows. I think I just wanted to rename the macro rather
than the module. As now in another sub part when I call this marco:
call cashflows(), it wont work


Please help!!:(
 
F

Franz Verga

Nel post *CarolineHedges* ha scritto:
Having a problem re: my 1st email.

I have renamed the module but now the macro is called for eg:
cashflowscashflows. I think I just wanted to rename the macro rather
than the module. As now in another sub part when I call this marco:
call cashflows(), it wont work
Click to expand...

Change back the name of the module.

I think this sould solve your problem...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula 2
excel date problem 3
Given a date, how do I get the 1st date and the last date of theprevious month? 5
Define variable range input for SUM() function 8
Re-post Formula 2
Difference between last date of month and given date 3
Problems 6
Month (MMM) from date (dd/mmm/yy) 1

Top