How to generate dates

M

Maddy1234

New Member
Joined
Mar 9, 2018
Messages
24
Hi all,

I have a rather peculiar problem. I have created a simple OT tracker.


BvSmdLL.jpg
[/IMG]

I want help in generating the data for the last two columns. Dates Worked Normal OT should tell which are the days a certain employee stayed back for OT on a weekday. So for example for Bob, this column will display "02/01/2018, 03/01/2018, 04/01/2018, 05/01/2018, 10/01/2018, 12/01/2018". For Bob, Dates Worked Double OT will display "06/01/2018, 07/01/2018, 13/01/2018".

Double OT is paid for OT covered in weekends.

I would like a formula where the last two columns are automatically updated when the data for Week3 and Week4 are added.

Your help is much appreciated. Thank you.

Maddy
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
This raises some questions:

1. Are the numbers 1,2,3,...,14 dates but formatted as "d" or are they numbers? It would be easier if they were dates.
2. What are the formulas you have in place for calculating the four numbers you already have in place (18, 10, 6, 3).
3. What version of Excel are you using. Please say "2016" because it has TEXTJOIN which makes this soooo much easier.

For example, I created something similar:


Book1
ABCDEFGHIJKLMNOPQRSTU
1Jan-18
2Week 1Week 2Normal OTWeekend OTNo. of Days Normal OTNo. of Days Double OTDates Worked Normal OTDates Worked Double OT
31234567891011121314
4Name
5Bob0321181009021018106302/01/2018, 03/01/2018, 04/01/2018, 05/01/2018, 10/01/2018, 12/01/201806/01/2018, 07/01/2018, 13/01/2018
6Jack
7John
8Rebecca
9May
Sheet1
Cell Formulas
RangeFormula
P5=SUMPRODUCT(--(WEEKDAY($B$3:$O$3,2)<6)*($B5:$O5))
Q5=SUMPRODUCT(--(WEEKDAY($B$3:$O$3,2)>5)*($B5:$O5))
R5=SUMPRODUCT((WEEKDAY($B$3:$O$3,2)<6)*($B5:$O5>0))
S5=SUMPRODUCT((WEEKDAY($B$3:$O$3,2)>5)*($B5:$O5>0))
T5{=TEXTJOIN(", ",TRUE,IF(WEEKDAY($B$3:$O$3,2)<6,IF($B5:$O5>0,TEXT($B$3:$O$3,"dd/mm/yyyy"),""),""))}
U5{=TEXTJOIN(", ",TRUE,IF(WEEKDAY($B$3:$O$3,2)>5,IF($B5:$O5>0,TEXT($B$3:$O$3,"dd/mm/yyyy"),""),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.


WBD
 
Upvote 0
Thank you so much for your reply WBD. To answer your questions :

1. The numbers were just text but I have now changed them to dates now.
2. The formulas I used are fairly simple sum functions which I have replaced with the formula you have given below.
3. Unfortunately I work with office 2013 and do not have access to office 2016. Can you please help me achieve the result in an alternative method? Furthermore it would be great if the code puts out "2nd, 4th & 14th" instead of "02/01/2018, 04/01/2018 & 14/01/2018"

Thank you so much for your help.
 
Upvote 0
Hi Maddy,

Does this have to be done with formulas or can I use VBA to write a User-Defined Function (UDF)?

WBD
 
Upvote 0
Absolutely any solution is fine mate. I would gladly take UDF to generate the data into the cell. Is it at all possible to make it happen automatically so when the user changes the data the dates will be changed accordingly?

Thank you so much for your time.
 
Upvote 0
Not to be a pain mate but I have one more request. Is it possible to append "NA" to the cells where there is no OT dates. Thanks.
 
Upvote 0
Busy weekend. Let me give this some thought and I'll try and do something either tomorrow or Monday.

WBD
 
Upvote 0

Book1
ABCDEFGHIJKLMNOPQRSTU
1Jan-18
2Week 1Week 2Normal OTWeekend OTNo. of Days Normal OTNo. of Days Double OTDates Worked Normal OTDates Worked Double OT
31234567891011121314
4Name
5Bob032118100902101810632nd, 3rd, 4th, 5th, 10th, 12th6th, 7th, 13th
6Jack
7John
8Rebecca
9May
Sheet1
Cell Formulas
RangeFormula
P5=SUMPRODUCT(--(WEEKDAY($B$3:$O$3,2)<6)*($B5:$O5))
Q5=SUMPRODUCT(--(WEEKDAY($B$3:$O$3,2)>5)*($B5:$O5))
R5=SUMPRODUCT((WEEKDAY($B$3:$O$3,2)<6)*($B5:$O5>0))
S5=SUMPRODUCT((WEEKDAY($B$3:$O$3,2)>5)*($B5:$O5>0))
T5=GetOvertimeDates($B5:$O5,$B$3:$O$3,TRUE)
U5=GetOvertimeDates($B5:$O5,$B$3:$O$3,FALSE)


Code: 
Public Function GetOvertimeDates(hoursRange As Range, datesRange As Range, weekdays As Boolean) As String

Dim thisCell As Long

GetOvertimeDates = ""

For thisCell = 1 To hoursRange.Count
    If hoursRange(thisCell).Value > 0 Then
        If (weekdays And Weekday(datesRange(thisCell).Value, vbMonday) < 6) _
        Or (Not weekdays And Weekday(datesRange(thisCell).Value, vbMonday) > 5) Then
            GetOvertimeDates = GetOvertimeDates & ", " & GetOrdinal(day(datesRange(thisCell).Value))
        End If
    End If
Next thisCell

If Len(GetOvertimeDates) > 0 Then GetOvertimeDates = Mid(GetOvertimeDates, 3)

End Function
Private Function GetOrdinal(day As Long) As String

GetOrdinal = CStr(day)

Select Case day
    Case 1, 21, 31
        GetOrdinal = GetOrdinal & "st"
    Case 2, 22
        GetOrdinal = GetOrdinal & "nd"
    Case 3, 23
        GetOrdinal = GetOrdinal & "rd"
    Case Else
        GetOrdinal = GetOrdinal & "th"
End Select

End Function

WBD
 
Upvote 0
wideboydixon said:
ABCDEFGHIJKLMNOPQRSTU
Normal OTWeekend OTNo. of Days Normal OTNo. of Days Double OTDates Worked Normal OTDates Worked Double OT
Name
Bob2nd, 3rd, 4th, 5th, 10th, 12th6th, 7th, 13th
Jack
John
Rebecca
May

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Jan-18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Week 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Week 2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]P5[/TH]
[TD="align: left"]=SUMPRODUCT(--(WEEKDAY($B$3:$O$3,2)<6)*($B5:$O5))[/TD]
[/TR]
[TR]
[TH]Q5[/TH]
[TD="align: left"]=SUMPRODUCT(--(WEEKDAY($B$3:$O$3,2)>5)*($B5:$O5))[/TD]
[/TR]
[TR]
[TH]R5[/TH]
[TD="align: left"]=SUMPRODUCT((WEEKDAY($B$3:$O$3,2)<6)*($B5:$O5>0))[/TD]
[/TR]
[TR]
[TH]S5[/TH]
[TD="align: left"]=SUMPRODUCT((WEEKDAY($B$3:$O$3,2)>5)*($B5:$O5>0))[/TD]
[/TR]
[TR]
[TH]T5[/TH]
[TD="align: left"]=GetOvertimeDates($B5:$O5,$B$3:$O$3,TRUE)[/TD]
[/TR]
[TR]
[TH]U5[/TH]
[TD="align: left"]=GetOvertimeDates($B5:$O5,$B$3:$O$3,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Code: 
Public Function GetOvertimeDates(hoursRange As Range, datesRange As Range, weekdays As Boolean) As String

Dim thisCell As Long

GetOvertimeDates = ""

For thisCell = 1 To hoursRange.Count
    If hoursRange(thisCell).Value > 0 Then
        If (weekdays And Weekday(datesRange(thisCell).Value, vbMonday) < 6) _
        Or (Not weekdays And Weekday(datesRange(thisCell).Value, vbMonday) > 5) Then
            GetOvertimeDates = GetOvertimeDates & ", " & GetOrdinal(day(datesRange(thisCell).Value))
        End If
    End If
Next thisCell

If Len(GetOvertimeDates) > 0 Then GetOvertimeDates = Mid(GetOvertimeDates, 3)

End Function
Private Function GetOrdinal(day As Long) As String

GetOrdinal = CStr(day)

Select Case day
    Case 1, 21, 31
        GetOrdinal = GetOrdinal & "st"
    Case 2, 22
        GetOrdinal = GetOrdinal & "nd"
    Case 3, 23
        GetOrdinal = GetOrdinal & "rd"
    Case Else
        GetOrdinal = GetOrdinal & "th"
End Select

End Function

WBD
Click to expand...

You are pure awesomeness mate. Got it to work exactly as I wanted. Thank you so much for your time.

If it's not too much trouble I have one last favor to ask you. I have tried so many various conditional formatting to achieve the following result.

An employee can do a maximum 12 hours of OT per week. And a maximum of 48 a month. An employee can only do a maximum of 3 hours of OT on a weekday and only 9 hours of OT on weekends. The OT sum for Saturday and Sunday should not exceed 9 hours. So I tried to come up with conditional formatting to color the cells Red or popup an error message whenever a number is entered violating the above criteria but I've had no luck.

For example following are acceptable scenarios.
ABCDEFGHI
Bob

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1st[/TD]
[TD="align: center"]2nd[/TD]
[TD="align: center"]3rd[/TD]
[TD="align: center"]4th[/TD]
[TD="align: center"]5th[/TD]
[TD="align: center"]6th[/TD]
[TD="align: center"]7th[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]

</tbody>

ABCDEFGHI
Bob

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1st[/TD]
[TD="align: center"]2nd[/TD]
[TD="align: center"]3rd[/TD]
[TD="align: center"]4th[/TD]
[TD="align: center"]5th[/TD]
[TD="align: center"]6th[/TD]
[TD="align: center"]7th[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]

</tbody>

ABCDEFGHI
Bob

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1st[/TD]
[TD="align: center"]2nd[/TD]
[TD="align: center"]3rd[/TD]
[TD="align: center"]4th[/TD]
[TD="align: center"]5th[/TD]
[TD="align: center"]6th[/TD]
[TD="align: center"]7th[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]

</tbody>

The following are not acceptable.

ABCDEFGHI
Bob

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1st[/TD]
[TD="align: center"]2nd[/TD]
[TD="align: center"]3rd[/TD]
[TD="align: center"]4th[/TD]
[TD="align: center"]5th[/TD]
[TD="align: center"]6th[/TD]
[TD="align: center"]7th[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"] 0 [/TD]
[TD="align: center"] 3 [/TD]
[TD="align: center"] 2 [/TD]
[TD="align: center"] 1 [/TD]
[TD="align: center"] 1 [/TD]
[TD="align: center"] 8 [/TD]
[TD="align: center"] 1 [/TD]

</tbody>

ABCDEFGHI
Bob

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1st[/TD]
[TD="align: center"]2nd[/TD]
[TD="align: center"]3rd[/TD]
[TD="align: center"]4th[/TD]
[TD="align: center"]5th[/TD]
[TD="align: center"]6th[/TD]
[TD="align: center"]7th[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"] 3 [/TD]
[TD="align: center"] 1 [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] 8 [/TD]
[TD="align: center"] 1 [/TD]

</tbody>

ABCDEFGHI
Bob

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1st[/TD]
[TD="align: center"]2nd[/TD]
[TD="align: center"]3rd[/TD]
[TD="align: center"]4th[/TD]
[TD="align: center"]5th[/TD]
[TD="align: center"]6th[/TD]
[TD="align: center"]7th[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] 9 [/TD]
[TD="align: center"] 1 [/TD]

</tbody>

<table class="wysiwyg_dashes" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;"="" width=""><tbody>[TR]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1st[/TD]
[TD="align: center"]2nd[/TD]
[TD="align: center"]3rd[/TD]
[TD="align: center"]4th[/TD]
[TD="align: center"]5th[/TD]
[TD="align: center"]6th[/TD]
[TD="align: center"]7th[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Bob[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody></table>
Can you please help me achieve a system to stop the user from entering a value that would violate the OT allocations.

Many thanks in advance for your help.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
List all staff names that worked under a manager on a certain date
Replies
5
Views
340
Fluff
Fluff
JenniferMurphy
  • Question Question
Help with coin-collecting sheet
Replies
3
Views
151
felixstraube
F
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
86
Dazzybeeguy
D
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
356
dilshod_k
D
Z
  • Solved
Conditional Format Date Range
Replies
4
Views
910
zero269
Z

Forum statistics

Threads
1,223,912
Messages
6,175,341
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top