date & time format

  1. A

    Filling column with Dictionary Keys

    Hello, I am trying to fill a column with distinct date values with different formats depending on the sheet (sometimes dd-mm-yyyy, sometimes mm-yyyy). I have made a Sub which fills Dictionary with distinct values in the correct format, and then copies them to the sheet. However, no all of the...
  2. P

    DATEVALUE Error

    Hello, I am trying to convert a date with a timestamp into a date value. However, although I tried multiple approaches, like using INT(A2), the value is still not recognized as a date value. And the weird part is that some of the original values are working as a date value when I copy/paste to...
  3. A

    Speed up code: convert datetime to just date

    Hello, I am trying to convert about 75 000 lines of datetime formate data (YYYY/MM/DD HH:MM:SS) into just dates (YYYY/MM/DD). The only way I have found to do this it to concatenate the YEAR(), MONTH(), DAY() parts of the data in one column into another column. However, with so many rows, this...
  4. A

    Help converting a date with a timestamp in excel

    Hello! In my office we use slack for the majority of our communication needs. With Slack you can build workflows that allow users to enter data which can then be imported into an excel sheet. Slack date/timestamps each submission in the excel file in a format which looks like this...
  5. R

    VBA code for recent file search and open with variable date and time in file name

    Dear all, I am looking for a vba code which can help me find a recent file uploaded in a path for example "D:\Test\" and copy the data from the file to current workbook open. The file name contains both the date and time of upload in its name. eg- "20210126_11_03_42_Test_2" and in the file name...
  6. O

    How to automatically run VBA code between specific hours for each week in MS Access application

    I have a MS Access application that is used in multiuser environment. I want to make maintanence every thursday between 15:00 and 16:00. At this point I have two issues to consider. Firstly, The users who may entered to application before 15:00. Secondly, the users who will be entering between...
  7. B

    Text To Columns vba producing different results on differents pc

    Hi all, I have a problem, I set up a macro to format a column (fixed-width) with a date format DMY. It works fine on my PC but on a colleague's pc, it isn't working. Here the code Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _...
  8. R

    Calculate a price for a item which was rented on different day's

    I Have a item " spoon " Price is €0.07 per day One customer who has ordered 50 spoons on 1.11.20 ( and still has not returned them). On the 7.11.20 he orders another 40 spoons. On the 14.11.20 another 20. On the 21.11.20 he returned 30 spoons ( I would charge him 21 days at € 0.07) On the...
  9. E

    Date subtraction with result in seconds and modular division problem

    I am trying to subtract a load of dates from an earlier date to end up with seconds which I can then do modular division on with the number of seconds in a sidereal day. This will give me time separation that I can sort to find similar location of points in the celestial sphere. I simply...
  10. H

    DATEDIF result in years and months - duplicate

    I need to create a mark book. =VLOOKUP(G3,'Yr5 Maths Aut'!$A$1:$U$112,MATCH(I3,'Yr5 Maths Aut'!$A$1:$U$1,0),0) The Vlookup table uses the following Ages 9 yrs 1 month, 9 yrs 2 months etc to 9 yrs 10 months. Total 9.00 9.10 9.20 ….. 9.90 9.10 9.11 10.00 10.1 The problem is...
  11. A

    Displaying Text

    Hi everyone, When I use the formula: ="The date is" & TEXT(M30, "dddd, mmmm yyyy") I encounter with this: The date is00, mmmm 2020 Although the M30 cell is in the date format, the formula did not work the way I wanted it to. I could not understand what happened? Any advice or suggestions very...
  12. E

    Separating Time and Date in Cells.

    I’m dealing with the timestamps from Google form, which come in the following format.
  13. A

    International Date Format Conversion and Pivot Grouping by Date

    Greetings all - I have a date format and pivot grouping issue, please: I've been given output in the format d/m/y 00:00. I believe this is standard Euro Area/Australian date format. This column, as given to me, is formatted as General. I have set my windows regional formatting to use Australia...
  14. E

    Exporting to CSV, messed up date format.

    Using this: Private Sub Create_CSV() Dim content As String Dim Rng As Range Set Rng = Range("A12:AS30") Dim Path As String Dim FileName1 As String Dim FileName2 As String Dim FileName3 As String Dim sWB As Workbook, _ sWS As Worksheet Dim dWB As Workbook, _ dWS As...
  15. D

    Extract date from text

    Hi Guys, could you help me with formula to get date from text as below ? from text: 30.5.2019 19:24 to this date format: 30-05-2019 7:24:00 PM Thanks a lot.
  16. I

    Date formatting

    Using a share file on Microsoft Teams Problem: I add data every day and my team use the data for reporting within the sharefile. Every day when we enter Teams and update the data, the dates we use revert to English (US) or Armenian. The date columns are formatted to dd/mm/yyyy using custom...
  17. C

    I have a column with Date and time in the same field, how to I split or change so I only have the date?

    HI all, I hope someone can help. I have a Dtae&time column with 04-07-2019 15:17:10. I only need the date (preferably as 04.07.2019). I have tried to simply reformat the date to another format but that does not work. Any ideas? Thanks in advance.
  18. A

    week/year (wwyy) format

    Hi everyone, I am looking for create a gantt chart to track different stages of projects, I tried rearranging a template I found that had the week already in the template (the only one i could find). Each stage has a start and end date, and the date has to be entered in the wwyy format. The...
  19. F

    Date and timeline

    Hi everyone, i have a problem i cant solve myself and google havent helped me so far, its a 3 step question and i hope you guys can help me out! i have multiple sheets with a timeline with custom dates as a string (yyyymm). i need to change this string format to a date format and after that i...
  20. A

    Dates show DMY in cell, but MDY in formula bar - won't sort or convert

    Hello - I'm using O365 in the US but have system time on my PC set for Australia (in case this matters…) WorkSheet #1 I have received two excel worksheets, one has dates formatted in DMY, and if I switch the formatting of any of those cells to General, I see that the resulting number (in...

Some videos you may like

This Week's Hot Topics

  • SUMPRODUCT active link formula
    Hi guys i have sumproduct formula for counting two range of number, i want count active cells of formula that linked to another sheet...
  • Block certain cells in condition met in cell A
    Hi there, trying to figure out step by step how to build macros and learn more. Now given that my other code was a mess, I figured I would...
  • IF FUNCTION FIRST, THEN WITH INDEX AND MATCH
    Hi, Below formula works well, =(INDEX('PRICE LIST'!$C$7368:$C$7679,MATCH(1,(WORKSHEET!O28='PRICE LIST'!$A$7368:$A$7679)*(WORKSHEET!P28='PRICE...
  • Match data from 3 columns to return data from the correct 4th column
    Hi there! I'm trying to have a cell auto-populate the data in a cell based on the data entered in 3 other cells. I've pasted a copy of the...
  • VLookup
    Hi everyone, I need to find the value from one sheet to another. So in Sheet A Field N5 I have a value (Spark) I want to find Spark on the Sheet...
  • Defining a range
    Private Sub Worksheet_Calculate() Dim Xrg As Range Set Xrg = Range("K1") If Not Intersect(Xrg, Range("K1")) Is Nothing Then MsgBox...

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
Top