date

  1. C

    Table column with date field

    I have a table created with one empty row at the start and one of the columns is a date in the format dd/mm/yyyy When a user inputs data and tabs down to create a new row the date column reverts back to mm/dd/yyyy Is there a reason for this even though I have set the format of the initial row...
  2. R

    Date Format Conversion Question

    I regularly download a .csv file which I open in Excel and then save as 97-2004 workbook (.xls). Contained in this .csv file is a column with dates which are formatted as short dates. I can't seem to find a way to convert these to long dates other than changing the format and then manually...
  3. C

    Month Sum based and dates and a name

    I have a spreadsheet called "Scrap Detail" where we log what items we scrap and against what work cell. Column A has the date and column B has a drop down to select the Cell. The value of the scrap is then totalled in Column F. We then have different work cells within the business where I have...
  4. E

    Return unique values based on multiple conditions and sorted by date

    Hello, I have data similar to whats shown in the "Orig Data' table. I would like to pull unique Names only, based on two criteria (Start Date <= today AND End Date >= today. I would also like the data sorted (oldest to newest) based on the Name's corresponding End Date . My goal is to have a...
  5. J

    Incorrect result when working with Max Date

    I need some help as I get an incorrect result and I can not solve it. I have a table with following fields: - Ordertype ("Actual" or "Forecast") - CustomerNumber - OrderNumber - DeliveryDate - Plant - Material - Volume I have following measures - Sum of Volume = SUMX ( FactData...
  6. C

    Use formulae to check that 2 variables on sheet A appear in the same row on sheet B

    Hi I am trying to use formulae to check that the order number (sheet A, cell B4) and week due (sheet A, cell Q4 as YYWW) values are found in the same row of sheet B. The order numbers are stored in column E of sheet B, the due date is stored in column O of sheet B as WW. Note the order number...
  7. K

    Query Editor - Issue with coverting date format when year changes when date format is Day DD MMM e.g Sat 28 Dec

    https://imgur.com/a/2bFUSl2 At the switch over to 2020 it just shows as an error using the following #"Changed Type" =Table.TransformColumnTypes(Data0,{{"Column1", type date}, Is there an way to handle this?
  8. M

    Power Query duplicate date error with no duplicates?!

    Excel 2016 64-bit - Suddenly my calendar file that loaded just fine is throwing errors that there are duplicate dates, so it can't join to the other DAX tables. The query appears just fine in PQ but blows up when I try to load to the Excel data model. These lines at the end don't make a...
  9. S

    Networkdays with start and end times on different days due to multi country shifts

    Hi, I am faced with this scenario where I need to calculate the number of minutes between two date & time stamps with the Start and End time in different hours . My scenario is as below Excluding Hours between 7:00 PM to 11:00 PM EST all other time, excluding Saturday and Sunday and...
  10. S

    Type mismatch using date in InputBox

    I make a request for a date and if it is a valid date (11/11/2019), partial date (11/11) or even a single number (11) “inputbox” can handle it. It will provide “date” formatted results. If I put in only “11/” and nothing else I get a type mismatch. How should/can I check for valid input? Your...
  11. Geo1126

    IF Last month

    Hi Guys, I think this is pretty basic, yet I still havent been able to figure it out. I want a formula that returns a cell value, if another cell value (date) occured last month. if the month column is blank or not last month return NA. A1 has date B1 has value I wanted formula to capture C1...
  12. S

    Calculate no of days to go back to reach a specific day name

    I know this should be easy, but I keep tying myself in knots over this one. I have a date selected by the User. I'm trying to find a simple way of working out how many days before that date is a specific "day name". For example if I take the date as 23/12/2019, I want to find out how many days...
  13. C

    End of Month / Max if is showing the incorrect date

    Good morning, I'm using the below formula to display the oldest date in a range (to the end of the month) if there is a % value in a designated cell range. =EOMONTH(MAXIFS('FA Afford'!I22:I33,'FA Afford'!E22:E33,">0"),0) In an older version of the document I'm using (where the cell ranges...
  14. S

    Macro to move zip file from one location to another, extract, then delete zip if possible

    Hi Can anyone help me by writing a macro that can move a zip file from a specific folder, put it in a new specific folder elsewhere on the drive, extract it in that location, then delete the zip? The delete is the least important part. When it moves it initially it would be best if it was cut...
  15. L

    SUMIF matching 2 cells

    Good afternoon all, I am having a problem as I modified my excel workbook and I am unable to find the sum I'm looking for. I keep getting #Value ! I have dates in cells P1 and P2, start date and end date. This is located on a sheet called Records. I have a datasheet that is full of data from 3...
  16. E

    Multiple Date Columns in Data Model Pivot Table

    Good Afternoon, How can I create 2 columns with distinct date ranges? I have 1 table with my dates and units and I want to show the units sold for 1 week in 1 column and the next week in the second column and then calculate the change WOW. I don't know how to have two date columns from one...
  17. T

    Conditional format if year in cell's date is same as current year

    I have a cell that contains a date in the format 21-Feb-21. I would like it to shade it green if the year in the date is the current year. I am going to have a second rule where if the year in the cell is before (less than) the current date than shade red. I believe I can do it simply but...
  18. A

    Formula to Return Training Status with Multiple Outputs

    I a table with 6 different input scenarios and need to return 4 different outputs depending on the input. The table data is all dates and I would like to fill the Status column with one of the following outputs. No-Value Complete On-Time Past-Due An example of the scenarios in the table...
  19. J

    Count # of days listed for month/year

    Hi! I have column A:A as date (dd/mm/yyyy) from Sales entry*, column B:B as amount and D1 through D12 are months & years (11/2018, 12/2018, 01/2019, 02/2019....) How do I calculate the sales entry by month and year in column D? *Note: No sales entry date if the amount is 0 or store is closed...
  20. R

    Macro with Message Box for Date Greater Than Today

    Hi there, I need a VBA to be used in Excel to check a couple of columns (F:F, H:H, N:N) to see if any cell in those columns contain a date that is greater than today and pop up a message. Could you please help? Thank you.

Some videos you may like

This Week's Hot Topics

Top