How to convert US (MM/DD) to UK (DD/MM) date format in Excel?

The system at work is using US date format and whenever I export any data into excel I have faced challenges since Excel does not know how to treat date column if it’s entered in different format.

Hence I have sourced only a rather simple solution – read below.

Image

Insert US date format values in Column A and in Column B you will get respective UK date format values. Drag cells down to as many as need.

Formula for cell B2:

=IF(A2=””,””,IFERROR(IF(CELL(“prefix”,A2)=”‘”,DAY(A2)&”/”&MONTH(A2)&”/”&YEAR(A2),MONTH(A2)&”/”&DAY(A2)&”/”&YEAR(A2)),DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))))

Hope this helps!

Advertisements

Need a longer than standard length macro and with ‘if this then that’ conditions?

Image

If you have ever tried to record a macro only to later discover that you’re getting ‘macro too long’ error message, you should consider to split a big macro project more that 1,400 lines) into several parts.  Once you record separate macros (or, alternatively, you can record the whole thing and later cut it manually in code into several macros – requires more than just a very basic skills and so not recommended for novices) you need to make a new macro which will ‘call’ part A, B, C etc. one after another to execute the full project.

In below example you can see visual basic command ‘call’ + [macro name] repeating several times – this runs each macro within the bigger picture. I have also used ‘If’ condition – you can set the criteria in the master macro file (xlsm – macro enable spreadsheet) and then using the drop down menu use your own way to customise your report.

See the example code below.

Sub Big Project()
'
' Big Project Macro
'
'
If Range("l16").Value = "Yes" Then
 Call Project_Presorting
End If
If Range("l12").Value = "Yes" Then
 Call Project_PartA
 Call Project _PartB
End If
If Range("l12").Value = "No" Then (this condition excludes option of running macro called Project_PartB)
 Call Project_PartA
End If
If Range("l18").Value = "Yes" Then
 Call Project_Part C
End If
End Sub

Renars

A Must Have For Any Credit Controller

In my line of work I have often come across the situation where we (the company I work for) have received a payment with no payment details and it is unable to match the payment to any open invoices.  When the customer has paid oldest-first or the list of open invoices is single-digit it is very easy to do the matching. However when you have to find five out of 50 invoices, things get trickier.

I identified the problem and search for the web and, as usual, Google has answer for (almost) anything.

I came across a VBA/ Macro-enable Excel solution where you enter number of possible  outcomes (1), target value / receipt (2), and list all invoices that should be matched (3) and with a click of a button you get your results.

Image

Example of how to match payment with a list of open invoices – this is extremely helpful for any Credit Controller / Sales Ledger

All you need is a basic knowledge of VBA code or how macros work in Excel:

  1. Open Excel and save as macro-enabled workbook.
  2. format the page to your liking (or similar to layout in my example above)
  3. go to developer tab and create a macro and paste the code (see below)
  4. create a button and assign the macro
  5. save the workbook and all done.

Code is below. I cannot remember where I found it but I am grateful to the wonderful person who made it.

Sub startSearch()
 'The selection should be a single contiguous range in a single column. _
 The first cell indicates the number of solutions wanted. Specify zero for all. _
 The 2nd cell is the target value. _
 The rest of the cells are the values available for matching. _
 The output is in the column adjacent to the one containing the input data.

 If Not TypeOf Selection Is Range Then GoTo ErrXIT
 If Selection.Areas.Count > 1 Or Selection.Columns.Count > 1 Then GoTo ErrXIT
 If Selection.Rows.Count < 3 Then GoTo ErrXIT

 Dim TargetVal, Rslt(), InArr(), StartTime As Date, MaxSoln As Integer, _
 HaveRandomNegatives As Boolean
 StartTime = Now()
 MaxSoln = Selection.Cells(1).Value
 TargetVal = Selection.Cells(2).Value
 InArr = Application.WorksheetFunction.Transpose( _
 Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Value)
 HaveRandomNegatives = checkRandomNegatives(InArr)
 If Not HaveRandomNegatives Then
 ElseIf MsgBox("At least 1 negative number is present between positive numbers" _
 & vbNewLine _
 & "It may take a lot longer to search for matches." & vbNewLine _
 & "OK to continue else Cancel", vbOKCancel) = vbCancel Then
 Exit Sub
 End If
 ReDim Rslt(0)
 recursiveMatch MaxSoln, TargetVal, InArr, HaveRandomNegatives, _
 LBound(InArr), 0, 0.00000001, _
 Rslt, "", ", "
 Rslt(UBound(Rslt)) = Format(Now, "hh:mm:ss")
 ReDim Preserve Rslt(UBound(Rslt) + 1)
 Rslt(UBound(Rslt)) = Format(StartTime, "hh:mm:ss")
 Selection.Offset(0, 1).Resize(ArrLen(Rslt), 1).Value = _
 Application.WorksheetFunction.Transpose(Rslt)
 Exit Sub
ErrXIT:
 MsgBox "Please select cells in a single column before using this macro" & vbNewLine _
 & "The selection should be a single contiguous range in a single column." & vbNewLine _
 & "The first cell indicates the number of solutions wanted. Specify zero for all." & vbNewLine _
 & "The 2nd cell is the target value." & vbNewLine _
 & "The rest of the cells are the values available for matching." & vbNewLine _
 & "The output is in the column adjacent to the one containing the input data."
 End Sub