date & time format

  1. 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...
  2. A

    Sorting Dates with General format and time stamp

    Hello All - I have been sent a worksheet with dates from a user in Australia. The date format is: 17/06/2016, so Day, Month, Year. And to be more specific, what I've been sent is: 17/06/2016 18:53, so D, M, Year and a time stamp. There are other dates that I need to merge and sort with these...
  3. J

    How to copy date from excel to word

    Hi, I have the below code which opens a word document and find words in column A and replaces it with the text in the adjacent cell in Column B. This works well. My issue is that when it copies a date from one of the cells to word, it displays the date as a number rather than a date. Public...
  4. M

    Finding Date Fails with .Find

    Hello, I am having a very strange issue where I select two dates (in the format mm/dd/yyyy hh:mm:ss) and a table is generated with data from the Start Date and End Date. There is a 'data storage' sheet, Sheet4, that has all potential dates to choose from in Row 4. There are 5 columns of data...
  5. P

    Adding Hours to a Date

    I need to subtract hours from a date/time value using a formula. The results I have tried dont seem to work. What i've tried: Using a formula in B2 like =A2-4/24 to reduce 4 hours doesn't work. It returns #VALUE ! I have also tried =DATEVALUE(A2) and it returns #VALUE ! I have also tried to...
  6. A

    Mixed Date Formats (DD/MM/YY and MM/DD/YY) in same Column

    Hello, - I have a spreadsheet with loads of rows - In column C, I have dates with mixed formats (DD/MM/YY and MM/DD/YY) - Also some of these dates are showing as text(i.e to the left of the cell P.S The dates are mixed format, I dont need to change them all, which is what will happen. I'll...
  7. I

    Convert Month name to date - last day of the month

    Is there any way to convert a Month name to date format? It should be the last day of that month and current year. Example: June should be converted to 06/30/2019 July should be converted to 07/31/2019
  8. S

    Read File Based on Date VBA

    Hi. I need to read a file, based on latest date. Using VBA. I really don't know how to do since before this I only put exact name of the filename. I know how to do it using, I do like this, Dim stamp As String = DateTime.Now.ToString("yyyyMMdd"...
  9. P

    Converting mm/dd/yyyy to mm-dd-yyyy

    Hi everyone, I've been stumped by this for a while now: when I try to convert a date value form mm/dd/yyyy to mm-dd-yyyy, and output it as a message box, the code works perfectly. However, when I try to put this value into a cell, it reverts back to the original formatting of mm/dd/yyyy. I know...
  10. M

    VBA Split cells date format

    Hi, I want to splits cells (text to columns) with a VBA Macro. I'm using the following code: Columns("A:A").Select Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote...
  11. J

    Date Swapping Formula

    Hey Guys/Gals, I have been using a date swap formula to basically swap the month and day between "/" slashes. =IF(ISTEXT(C7),DATE(RIGHT(C7,4),LEFT(C7,SEARCH("/",C7)-1),MID(C7,SEARCH("/",C7)+1,2)),DATEVALUE(TEXT(C7,"mm/dd/yyyy"))) I originally was working on the formula for sample dates such...
  12. T

    Formula or Formatting to return a value based on a date and 18 weeks in the future from that date

    Hi All, I am new here and admittingly not the greatest using excel. I am having some issues with a workbook that I am currentlyworking on. I need a formula that looks between 2 date ranges to returna value. E.G - If the received date is 18 weeks from the running timethe value is compliant, if...
  13. Y

    Format Today() in a string

    To show todays date the formula =Today() in a cell returns just that... A formatted date : 1/13/19 I have a cell to show the week number and todays date <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} Page {margin:.75in .7in .75in .7in...
  14. D

    Date in Cell with Text

    Hello - I'm trying to calculate headers for a workbook, where we have a new header by date. The headers would be named "Key Accomplishments: 11/25/18"... "Key Accomplishments: 12/09/18", etc. Right now my formula is: =LEFT($O$1,20) &" " & RIGHT(O1,10)+14 which returns: Key Accomplishments...
  15. B

    Using hire date to convert either a year later or the first of the year based on the hire date

    Hello, I am trying to do a vacation accrual worksheet base off of a start date, or the first of the year. So I need the cell to populate either the hire date or 1/1/18 if they have already been employed for one year. So in Cell B5 I have the hire date in cell C5 I have the termination date (or...
  16. S

    Find Hours lapsed from two Date with time stamp

    Hi All, I need help with finding the hours lapsed with a formula or VBA. I have 100+ rows in excel where two columns have dates with time and I need the difference in business hours. It needs to exclude Weekends and US holidays while calculating the business hours it would be great (optional)...
  17. W

    Dates in an Array Constant for comparison

    Hello All, I am drawing a blank. I need an example of using an array constant with a set of 10 days. I will use the array constant to determine if a date matches any of the dates in the array constant. This is part of an application that I am helping write for a non-profit organization so that...
  18. A

    Wildcard in IF statement - find date and reformat

    I have a "comments" column that will randomly include comments and a date string */* (eg. A1 = "del 5/20", A2 = "expected delivery 6/1", A3 = "4/7", A4= "delivery 11/01", etc.) I want to find that date string in "comments column" and post in the adjacent column as standard date format...
  19. M

    Increment a date by a number of days, months or years

    Hi, I have a date which I will need to increment the date by a day, month or year from the actual date. This is based of a list of fields below and is different per the input. Also each resultant date has to be a business day i.e not a weekend day or a bank holiday. I will forgo the Bank...
  20. G

    Date, Time calculation

    Hi, I am calculating the hours, min (calling it opportunity) by multiplying available slots of 2 hrs and no of slots as 20. This means 20 hrs available time. Now, i have sum of time spent (start date + time & End date + time) by 4 people. I have to calculate gap : [available time] minus [sum...

Watch MrExcel Video

This Week's Hot Topics

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
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 "".
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