CODEWITHSUNDEEP
vbaexcel
Roy E.
Roy E.

Reputation: 1

Vba to create day of occurrence (not Day of week) ie: Day 1, Day 2, etc by Date

A vba-excel macro to modify my report from the current to desired format in the worksheets attached.

Summary

I would like to add a column as "Day of occurrence"---ie: Day 1...Day Z based on the date/time column to be able to use a Pivot table to identify all codes that were used on each day of service for the entire stay for each account number.

There are 40K rows of account number with various days of services. The number of columns should remain the same as in the example.

Current worksheet

Current worksheet

Desired Worksheet

Desired Worksheet

Upvotes: 0

Views: 223

Answers (5)

Vityata
Vityata

Reputation: 43585

You may use this custom function in VBA. It would give you the consequent day in the year.

Function d_day_consequent(my_date As Date)
    d_day_consequent = my_date - DateSerial(Year(my_date), 1, 1)
End Function

enter image description here

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

I will add this one:

Use the following formula in C:

="DAY " & DAY(E2 - MIN(IF(A:A = A2,E:E)))+1

It then does not matter if the order gets messed up.

To build on @pnuts comment if you want to keep the underlying number as a number then use this formula:

=DAY(E2 - MIN(IF(A:A = A2,E:E)))+1

And use the following custom format on the range:

"Days " 0

It will place the word in front of the number but keep the number as the entry so that any math can be done without removing the words.

Upvotes: 0

Ollie
Ollie

Reputation: 337

This might be overkill.... but this is what I came up with....

Function DayOfOccurrence(InputDate As Date) As String

    YearEntry = Year(InputDate)
    MonthEntry = Month(InputDate)
    IsLeapYear = -1
    On Error Resume Next
    IsLeapYear = CDate("2 / 29 /" & YearEntry) 'Hack to see if it's a leap year. Variable IsLeapYear will remain -1 if NOT a leap year.
    On Error GoTo 0

    If IsLeapYear = -1 Then 'Is Not a Leap Year
        If MonthEntry = 1 Then
            Base = 0
        ElseIf MonthEntry = 2 Then
            Base = 31
        ElseIf MonthEntry = 3 Then
            Base = 59
        ElseIf MonthEntry = 4 Then
            Base = 90
        ElseIf MonthEntry = 5 Then
            Base = 120
        ElseIf MonthEntry = 6 Then
            Base = 151
        ElseIf MonthEntry = 7 Then
            Base = 181
        ElseIf MonthEntry = 8 Then
            Base = 212
        ElseIf MonthEntry = 9 Then
            Base = 243
        ElseIf MonthEntry = 10 Then
            Base = 273
        ElseIf MonthEntry = 11 Then
            Base = 304
        ElseIf MonthEntry = 12 Then
            Base = 334
        End If
    Else 'Is a Leap Year
        If MonthEntry = 1 Then
            Base = 0
        ElseIf MonthEntry = 2 Then
            Base = 31
        ElseIf MonthEntry = 3 Then
            Base = 60
        ElseIf MonthEntry = 4 Then
            Base = 91
        ElseIf MonthEntry = 5 Then
            Base = 121
        ElseIf MonthEntry = 6 Then
            Base = 152
        ElseIf MonthEntry = 7 Then
            Base = 182
        ElseIf MonthEntry = 8 Then
            Base = 213
        ElseIf MonthEntry = 9 Then
            Base = 244
        ElseIf MonthEntry = 10 Then
            Base = 274
        ElseIf MonthEntry = 11 Then
            Base = 305
        ElseIf MonthEntry = 12 Then
            Base = 335
        End If

    End If

    DayOfOccurrence = "Day " & Base + Day(InputDate)

End Function

Upvotes: 0

A.S.H
A.S.H

Reputation: 29332

Assuming:

  • Account Number in column A

  • day of occurrence in column C

  • date & time in column E

And also assuming that the day of occurrence is not the "day of month", but simply a sequence of different days (If that is not the case, simply use Scott's comment).

You can use this formula to get the desired day number, but without the "Day " prefix.

Type this formula in cell C2 then copy and paste it to all cells of column C:

= IF(A2<>A1, 1, IF(E2=E1, C1, C1+1))

If you insist to display it as "Day x", the formula is a bit more complicated but it works (Again, enter this formula in cell C2 then copy/paste it in the other cells of column C):

=IF(A2<>A1,"Day 1",IF(E2=E1,C1,"Day "& (1+RIGHT(C1,LEN(C1)-4))))

p.s.: I would not recommend to prefix it, since it is numerical data so let it stay numerical...

Upvotes: 0

GavinP
GavinP

Reputation: 687

On the assumption that Day 1 is the earliest day in your Date & Time Column and that column is in Column E then this formula would do it ="Day " & E2-MIN(E:E)+1. Obviously this is input into whichever column you want, in row 2, and then filled down.

Upvotes: 1

Related Questions