PDA

View Full Version : If Sat or Sun, then Monday



marreco
03-09-2012, 07:11 AM
I have a spreadsheet that compares the data from tab "Operações" and generates a flow on the tab "Fluxo", this three capstan in a code module "Create_flow."

But this code parses the data in column "F", and estimates against time.

but when I have a date that falls on Friday, in the "I" the result is like Sunday, but can not should be on Monday.

See line 2 of the tab "Fluxo."


He was made a purchase on 28/02/2008 after 31 days (column "F"), should fall on 31/03/2008 (column "I").

The dates in Brazil is (dd / mm / yyyy) and not (mm / dd / yyyy).


So what I want is if the dates in column "I" tab "Fluxo" falls on a Sunday or s'bado to be changed to Monday.

Thank you.

Bob Phillips
03-09-2012, 07:38 AM
Try this formula in H2

=C2+F2+(WEEKDAY(C2+F2)=1)+(WEEKDAY(C2+F2)=7)*2

Bob Phillips
03-09-2012, 07:39 AM
You could also use

=WORKDAY(C2+F2-1,1)

marreco
03-09-2012, 07:46 AM
I would like to implement within the code.

How do I enter this code?
Sub cria_fluxo()

Dim Area As Range
Dim destino As Range
Call Limpa_fluxo
Set Area = Worksheets("Operações").Range("a2:f2000")
Set destino = Worksheets("Fluxo").Range("a2:f6000")
j = 1
i = 1
While Area.Cells(i, 1).Value <> ""
For k = 1 To 6
destino.Cells(j, k).Value = Area.Cells(i, k).Value
Next k
num_parcelas = Area.Cells(i, 7).Value
data_opera = Area.Cells(i, 3).Value
wtaxa = Area.Cells(i, 5).Value
wvalor = Area.Cells(i, 4).Value / num_parcelas
destino.Cells(j, 8).Value = wvalor
destino.Cells(j, 9).Value = data_opera + Area.Cells(i, 6).Value
destino.Cells(j, 10).Value = wvalor * (1 - wtaxa)
destino.Cells(j, 7).Value = 1
For k = 2 To num_parcelas
j = j + 1
For z = 1 To 6
destino.Cells(j, z).Value = Area.Cells(i, z).Value
Next z
destino.Cells(j, 6).Value = k * 30
destino.Cells(j, 7).Value = k
destino.Cells(j, 8).Value = wvalor
destino.Cells(j, 9).Value = data_opera + 30 * k
destino.Cells(j, 10).Value = wvalor * (1 - wtaxa * k)
Next k
j = j + 1
i = i + 1
Wend
'
' For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
' TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
' Next

Call Marca_fluxo
Call Atualiza_resumo

End Sub




Thank you

marreco
03-11-2012, 04:09 AM
Hi
So does anyone have any idea how to help me.
Thank you!