Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and Text in one formula
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 -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=554904 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and Text in one formula
Nel post news:CarolineHedges.29un1b_1151056202.6671@excelfo rum-nospam.com
*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 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and Text in one formula
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? "CarolineHedges" wrote: 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 -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=554904 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and Text in one formula
Nel post
*Franz Verga* ha scritto: Nel post news:CarolineHedges.29un1b_1151056202.6671@excelfo rum-nospam.com *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 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. 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and Text in one formula
Nel post
*Franz Verga* ha scritto: [cut ="Nav at "&TEXT(DATE(YEAR(D3),MONTH(D3),0),"gg mmmm aaaa") 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and Text in one formula
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 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 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and Text in one formula
Thanks a lot Franz Franz Verga Wrote: 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 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 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 -- Abanana ------------------------------------------------------------------------ Abanana's Profile: http://www.excelforum.com/member.php...o&userid=35529 View this thread: http://www.excelforum.com/showthread...hreadid=554904 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and Text in one formula
Thank you!!! -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=554904 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and Text in one formula
Having a problem 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!!:( -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=554904 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and Text in one formula
Nel post news:CarolineHedges.29uym4_1151071254.1113@excelfo rum-nospam.com
*CarolineHedges* ha scritto: Having a problem 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text File creates incorrect Date format | Excel Discussion (Misc queries) | |||
Count items when specific text and date criteria are met | Excel Worksheet Functions | |||
HOW DO I ENTER THIS FORMULA TO CONVERT TEXT TO DATE =DATEVALUE(, | Excel Worksheet Functions | |||
Can a conditional formula indicate "date added" next to a text? | Excel Worksheet Functions | |||
Formula with text and reference to a date cell | Excel Discussion (Misc queries) |