0

I'd like to translate a billing report from Portuguese(Brazil) to English. It is accurate by months and it is an automated sheet.
A contract may start on January, but it's commonly started in the other 11 months too.

If I set the first month as April, the eleven other on right keep growing (May, June, July...) until March.

I've created this formula: 

=PROPER(TEXT((B1&1)+31;"mmmm"))

for the second to the twelfth month.

However I use it in Portuguese(Brazil), and it works very well. But when hitting an English month, it doesn't really work.

I've tried [$-409] and it does not work they way I want it to.

If I use this, it goes like, Janeiro and February, and error on the next months.

I want to write January down and receive February, March and so on.

It might work on your machine, try it with other languages too, please.

Note: VBA won't work, I need to send it to my customers.

Hick
  • 122

2 Answers2

1

I think what you want isn't possible in the way you want.

As per your comments, you can't change the system locale settings because this document can be used on other computers where this isn't an option

The only solution I can think of is to use VBa

In this example, I am looking through the entire worksheet and replacing words for the equivalent

First off, take a back up of the file. You can't undo VBa

Sub ChangeTheNameMeHearties()

Dim cell As Range

For Each cell In ActiveSheet.UsedRange
    Dim s As String
    Select Case LCase(cell.Value)

        Case cell.Value = "january"
            cell.Value = "Janeiro"

        Case cell.Value = "february"
            cell.Value = "Fevereiro"

    End Select
Next

End Sub

You will need to add the rest of the months but it should be straight forward, and then think of how they 'translate'

Also see How do I add VBA in MS Office?

You could even use a case statement like

    Case cell.Value = "january"
        cell.Value = "Janeiro"

    Case cell.Value = "janeiro"
        cell.Value = "January"

    Case cell.Value = "february"
        cell.Value = "Fevereiro"

    Case cell.Value = "fevereiro"
        cell.Value = "February"

This will mean, each time you run it, you'll toggle between the languages

Community
  • 1
Dave
  • 25,513
0

I already fixed, sorry for lateness.

I fixed using

=PROPER(TEXT((DATE(2014,B3,1)),"mmmm"))

where "B3" is the month number, which changes only once, retrieved from Pivot Table.

The following month receives +1, instead "next month".

=IF(B3<12,B3+1,1)

The IF is used to restart from 1 if the month before is already 12. "Without if, it'd still keep going up beyond 13"

Dave
  • 25,513
Hick
  • 122