6 Replies Latest reply on Jul 27, 2009 1:38 PM by sarah1965

# Calculation field whose result must be date, from looked-up text field (eg. January)?

### Title

Calculation field whose result must be date, from looked-up text field (eg. January)?

### Post

Help! Can I specify a calculation field that looks up text in another table, and, where that table includes specific text, eg. January, inputs specific date e.g. 01/01/2010? How can I do this? With thanks, S

• ###### 1. Re: Calculation field whose result must be date, from looked-up text field (eg. January)?
Could you be more specific? It's not difficult to convert "January" to 1, "February" to 2, etc., but to make it a date you also need a day and a year - where are these supposed to come from?
• ###### 2. Re: Calculation field whose result must be date, from looked-up text field (eg. January)?
Well, I had hoped I might be able to input an instruction "where (text field) reads January in this record, input (date field) 01/01/2010 here. Maybe I'm being overambitious?
• ###### 3. Re: Calculation field whose result must be date, from looked-up text field (eg. January)?

sarah1965 wrote:
Maybe I'm being overambitious?

No, you're being vague. Why 2010, and not 2009 or 1544? Perhaps it would help if you explained the purpose behind this.

If you ALWAYS want day 1 and year 2010, you can use a calculation (result is Date) =

`Date ( Ceiling ( Position (  "janfebmaraprmayjunjulaugsepoctnovdec"  ; Left ( Textfield ; 3 ) ; 1 ; 1 ) / 3 ) ; 1 ; 2010 )   `

• ###### 4. Re: Calculation field whose result must be date, from looked-up text field (eg. January)?
I'm dealing with a database full of tasks all planned for next year which is why 2010. People have input a month in 2010 or a Quarter when they expect the activity to take place. I need to convert this info into two fields - StartDate and EndDate, so e.g. if the text field reads January, I'd like the StartDate field to read 01/01/2010 and the EndDate to read 31/01/2010. If I can specify this in a calculation field that would be great, but I'm not sure I can? PS, I'm not a programmer ..
• ###### 5. Re: Calculation field whose result must be date, from looked-up text field (eg. January)?

sarah1965 wrote:
if the text field reads January, I'd like the StartDate field to read 01/01/2010

That's what the above formula will do.

For EndDate, you can use =

```Date (
Ceiling ( Position (  "janfebmaraprmayjunjulaugsepoctnovdec"  ; Left ( Textfield ; 3 ) ; 1 ; 1 ) / 3 ) + 1 ;
0 ;
2010 ```

• ###### 6. Re: Calculation field whose result must be date, from looked-up text field (eg. January)?
Tha solves it! Thank you!