date & time format

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

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

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

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

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 vb.net, I do like this, Dim stamp As String = DateTime.Now.ToString("yyyyMMdd"...

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

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

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

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

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

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

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

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

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

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

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

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

I cannot find an exact answer to handle the following dates: 3/30/2012 12:00:00 AM 04/02/2012 12:00:00 AM Using Excel 2016 I tried 1- Formatting with custom dates including [$-409]mm/dd/yyyy hh:mm:ss AM/PM;@ 2- Various versions of LEFT(TRIM(RIGHT(SUBSTITUTE...

Hello, Despite the below standing code (Private Sub Process_XLS_Files) copies a date like 2-1-2017 it turns it into 1-2-2017. I have tried to get it fixed by add in an extra piece of code, but that did not help. Both with function of Dateformat as Numberformat. Look forward to your reply...

Hi, I'm trying to import a csv file with dates formatted like: Jan 1, 2017 6:01:12 AM PST How do I transform those dates when importing in PowerBI (and powerpivot)?