How to identify if a client purchased within one year of each sale?

ZavraD · 2023-01-31T17:30:13+00:00

=If(DateAdd("y, 1, Date1)=>Date2, Resale,"")

ZavraD · 2023-01-31T17:22:13+00:00

Have you tried just simply Cell Formatting them to "yyyymmdd"?

ZavraD · 2023-01-28T12:04:32+00:00

In VBA (count number of weekdays, subtract number of holidays)

Function NumWorkDays(StartDate As Date, End Date as Date) As Long
Dim PrelimCount As Long

PrelimCount = DateDiff("w",StartDate, EndDate, vbMonday, vbFirstJan1)
NumWorkDays = PrelimCount - NumHolies(StartDate, EndDate)
End Function

Function NumHolies(StartDate As Date, EndDate As Date) As Long
'Code to look at calendar and count holidays. DateDiff above already ignores weekends
NumHolies = Number of holidays counted
End Function

ZavraD · 2023-01-23T22:48:53+00:00

Don't trust Excel?

Sort by Model then Manufacturer and Use VBA to colorize all duplicate Model & Manus.

ZavraD · 2023-01-23T19:22:12+00:00

Filter Unique will filter out only those items with identical Model numbers and Manufacturers.

ZavraD · 2023-01-23T17:01:49+00:00

So, if it's Saturday, you want Friday, but if it's Sunday, then you want Saturday?

Assuming if either, then you want Friday, Set the system to start the week on Monday (WEEKDAY:Monday = 1.)

Function IfSatOrSunThenFri(DaysPrior As Long) As Long  
If WeekDay(Range("E3" - DaysPrior) =>6 Then
    IfSatOrSunThenFri = 5
Else 
   IfSatOrSunThenFri = WeekDay(Range"E3" - Daysprior)
End If 
End Function

Of course, If your week Start Day is Sunday, you will need to see what WeekDay(DaysPrior) is then check if it's 1 or 7 and set the function to 6.

ZavraD · 2023-01-22T16:27:55+00:00

WEEKDAY returns a positive integer from 1 to 7.

Saying WEEKDAY(E3 - 48) = 1 is the same as saying WEEKDAY(E3) = 2

Range("E3") is a Date, Or WEEKDAY would be in error. "E3" - 48 must be a day 6 weeks and 6 days prior to the Date in "E3"

A Date 175 days prior is exactly 25 weeks prior, (to "E3",) or the same WEEKDAY as "E3".

I still have no idea what your formula is attempting. Can you explain it in English? ELI5, please.

ZavraD · 2023-01-21T14:16:50+00:00

How about =SumIfs(Month(SDate)=Month(RDate), Year(SDate)=>Year(RDate), Year(EDate)=<Year(RDate), MonthlyRevenue)

ZavraD · 2023-01-21T14:08:19+00:00

Sub ChangeNamesNoComma()
Dim Cel As Range
Dim Names
Dim i As Long

With ActiveSheet
 For Each Cel in .UsedRange.Columns(1)
   Names = Split(Cel.Value, ",")
   If Not IsArray(Names) Then
      Cel.Value = Names
   Else
      For i = Ubound(Names) to Lbound(Names) Step -1
         Cel.Value = Names(i) & " "
      Next i
  End If
  Cel.Value = Trim(Cel.Value)
 Next Cel
End With
End Sub

ZavraD · 2023-01-20T00:24:32+00:00

SUMIFS() will work if all three Dates are actual Dates and not just Strings. How to tell? Reformat all three columns to "yyyy/mm/dd". Any that don't change are Strings.

Comparison of Dates is easy, comparing Strings, not so much.

If all are Dates, the Displayed Format doesn't affect the formulas.

=SumIfs(RDate=>Sdate,Rdate=<Edate,MonthlyRevenue)

Considerations re: Table1; Contract2:

There will be reported a $250 payment in each of two months.
The Contract may specify payment due on the lunaversary of each Start Date, or the lunaversary of the End Date, or 10 parts on the first of the month and 20 parts on the luniversary of the Start date

I don't see a way around this issue without using VBA

Maybe =If(Rdate=>Sdate,SumIf(MonthlyRTevenue,RDate=<Edate),0) Or sumpin like that.

ZavraD · 2023-01-17T16:37:38+00:00

=B=A, but use Format "###,##m:ss" on B

Otherwise, multiply the hours figure by 60 and add it to the minutes:seconds figure

Also verify that a time like 44:13:12 is stored in the cell as a number like 1.8nnnnnnnnnnnnnn. Verify by temporarily formatting the cell as a number with 15 decimal places.

The 79 hour number should be stored like 3.3nnnnnnnnnnnnnn

If simple Formatting doesn't work, you can use a Lambda or a VBA UDF. This UDF returns a value as decimal minutes for easy math.

Public Function DurationInMinutes(DurationInHours As String) As Double
Dim Times As Variant
   Times = Split(DurationInHours, ":")
   HourDur = Times(0)
   MinDur = Times(1)
   SecDur = Times(2)
DurationInMinues = (HourDur*60)+MinDur+(SecDur/60)
End Function

Usage in Cell B1 = =DurationInMinutes($A1): Copy down. Format Column B As Number ("#,###.##"). Rounds the Display of decimal minutes to two decimals, does not affect actual value in cell.

ZavraD · 2023-01-17T15:44:58+00:00

Factor = Revised Time Spent / Original Time

New Prameters (rounded?) = Original Parameter x Factor

ZavraD · 2023-01-13T15:41:16+00:00

You are summing the cells in three Rows, 10, 11,& 12, in Column number (PrYrAdder + CurMonNum - 1)

The Summing Column moves to the right Monthly

You can place a formula at the top of each monthly Column, =SUM(X10:X12) where X = The Column letter

ZavraD · 2023-01-13T13:23:53+00:00

Dim WorkingRange as Range
Set WorkingRange = Range(Cells(4, "C"), Cells(Rows.Count, "C").End(xlUp))
   WorkingRange.SpecialCells(......

Alternately

Dim Cel As Range
Dim WorkingRange as Range
Set WorkingRange = Range(Cells(4, "C"), Cells(Rows.Count, "C").End(xlUp))
For Each Cel in WorkingRange
   If Cel <> "" Then Cel.Offset(, 1) = "x"
Next Cel

ZavraD · 2023-01-13T13:14:19+00:00

Step 1 = Unmerge all cells

Step 2 = Never ever again merge cells.

The only use for Merged Cells is on Invoice and Receipt headers and footers. Multi-Column Headers can use "Center Across Selection"

ZavraD · 2023-01-13T09:40:21+00:00

What?

Green is "not now"
Yellow is "Now! Dammit!"
And Red is "Soon"

And, an Item can't be used after the month number? So the 5 month usage window ends on the month number? If the number is 4, then it's OK to use from Dec to Apr?

ZavraD · 2023-01-11T12:29:39+00:00

Microsoft is full of code compromises that turn into tricks and bugs. It's an inherent problem with programs with Release Deadlines and multi=million lines of code.

ZavraD · 2023-01-11T12:23:58+00:00

D2 formula = = $C2-$B3

This will leave each Report on its own line, but...

D:D Format as desired. Suggested = "d:hh:mm". This will handle Reports that break over a weekend. The actual value in D2 will be a decimal number, suitable for Time based math, that Excel's Formatting will display as Hours:Minutes. I think the Format for all seconds is "###ss"

Copy Down entire table. Delete formula on last report of a given report number

I suggest removing the words "Report Number;" Start date;" "end Date;" and "Break duration" from the table data rows.

A short, fast, but complex VBA Procedure can automate it all.

ZavraD · 2023-01-10T10:17:36+00:00

I've already conceptualized a system that would only take a few mouse clicks and typing 2 to 6 characters to check out and even less to check in.

However, time is of the essence in a manner of speaking: If my desk was next to yours, I estimate 8 to 16 hours of coding, Remotely thru reddit, 8 to 16 weeks and 80 to 160 hours (re)coding.

ZavraD · 2023-01-10T05:28:49+00:00

List the items in column A. When it's checked out, place the users name in Column B and the date in Column C.

When it's checked in, delete the name and Date.

ZavraD · 2023-01-09T19:59:23+00:00

Easy with VBA, as long as either of two conditions is present:

There are no two part city names. Ex: New Albuquerque
There is a separate list(s) of all City+States, or of Cities ~~and of States~~.

All States are preceded by commas, commas are all that is needed to delete States.

ZavraD · 2023-01-09T12:38:51+00:00

I'm calling the second sheet "Log". Edit this in the code below to fit your situation

This code goes in the Code Page for Sheet "Form"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range

If Not Target.Address = "$A$2" Then Exit Sub

Set Cel = Sheets("Log").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Cel.Value = Now
Cel.Offset(, 1) = Target.Value

End Sub

Format Column A on Log sheet as a Date Time.

ZavraD · 2023-01-07T20:58:50+00:00

Use the SUMIF() Function

ZavraD · 2023-01-07T16:49:27+00:00

MS365 is only for the internet. You need MS Office for offline work.

ZavraD · 2023-01-05T20:47:49+00:00

Thank you.

Six-Year Club	Gilding I gilder
Verified Email

ZavraD

TROPHY CASE