time and date

Excel 2019 In cell A1 we have timestamp 23/06/2018 12:02:18 No in cell B2 we want to add four hours in but if timestamp is 23/06/2018 14:00:00 and office open and close time is 11:00 am to 5:00 pm then it will add four hours, according to office working hours 24/06/2018 13:00:00 and if...

I'm using a custom cell format in two cells (mm/dd/yy hh:mm). These cells are used to calculate elapsed time. Users are instructed to input 24-hour time (e.g. 1:00 PM is 13:00); however, they are managing to bypass my attempts to prevent the document from being saved with an invalid date...

Hi everyone. Please help me understand why my VBa does not work with AutoSum but it works when i put in figures manually in the field. The VBa is as following Private Sub Worksheet_Change(ByVal Target As Range) ' Adds Date when Status changes in Column G21 If Target.Count > 1 Then Exit Sub...

Hi All, I am having some trouble adjusting the hours based on Time Zone. For context, I have a dataset where the date / time in column AC is all in EST. I want to adjust this based on Time Zone. In column AK I used =TIME(HOUR(AC2),0,0) to pull out the hour + AM / PM. However, I want to adjust...

Good morning the community. I want to get the same data time for each index. Example: 01-01-2009 02-02-2009 03-03-2009 ...................... But, I have this. See below. ABCDEF1Date^FCHI CloseDateBZ=F CloseDateEURUSD=X...

I have a Cumulative Time field that formats as [h]:mm:ss that aggregates time in hours. I am needing to extract the time portion to convert it to a Text or String value. For example, a cumulative time total of 95:31:00 shows as "1/3/1900 11:31:00 PM" in the formula bar which is not what i want...

Currently using the following on Windows to get GMT/UTC time but I need a way of doing this on MAC as well. Thanks in advance. Function UTC() As Date Dim dt As Object Set dt = CreateObject("WbemScripting.SWbemDateTime") With dt .SetVarDate Now UTC = .GetVarDate(False) End With...

Hi, I have two columns, one with a date and time (column A) and one with a status (column B). I want a third column(Column C) to say if the date and time in column A is less than today's date and the status in column B is not equal to 'COMPLETED' then move the date to today's date at 07:00:00...

I am trying to write code to copy a date range(with times included) to another worksheet in the same workbook. Example: eachdate range in column E would include only one month (say June dates) and only thatdata would go to a new worksheet. Then it would move to the next month incolumn E (say...

Subtract dates and times for overtime ...and also consider weekends and holidays PLEASE PLEASE HELP I need help with a formula to deduct ie dates and times to calculate regular time and overtime and multiply the regular time with the basic salary of R18.75 (Rand)....and Saturdays with...

Hello all, I'm in dire need of finding a better solution to this problem: I am in need of caclulating the time between multiple time ranges. Time Range A is a recurring schedule for the month of November, that allocates specific time slots to users, who are then allowed to login during these...

Hello - #1) I am trying to derive the MODE of a sequence of times of day. In other words, I'd like to see what times of day are the most frequently occurring times, from a list of observed times. I have been given the following format to convert: 12:00:00 EDT I imagine it does need to be...

I am trying to make this formula work: =IF(AND($A$1<=0.451388888888889,$A$1>=0.397916666666667),1,2) $A$1 is the current time. The formula =now() . It is 1/25/2018 20:03 a the time of this post. 0.451388888888889 is one time. It is 1/0/00 10:50 0.397916666666667 is the other time. It is 1/0/00...

Hi I get sent a csv file with a cell containing the date and time like this "30.11.2017 17:40:41", remember I'm in England so dd/mm/yyyy is the format we use. If I use the two commands below I can get just the date as 30/11/2017 but Excel doesn't recognise it as a date until I click in the...

Hello, How can I calculate the total of each allowance the employee is entitle during the given period? Start Time: 22/Mar/17 12:50 End Time: 30/Mar/17 18:50 Breakfast Between: 7:30 and 9:30 Lunch between: 12:30 and 14:30 Dinner between: 19:30 and 21:30 Thanks in advance

I have no experience editing macros. I can make two things work individually (sort of) but can't get them to work together. What I want is to have a button that says, "Start." The associated macro finds the first empty cell in a particular column (let's say column B), selects it, and inserts...

Hi, So im trying to do a LOOKUP or INDEX/MATCH against date and time values, ive combined them to produce the number that excel works in but it doesnt recognise them as the same number even though the lists are produced exactly the same way? I dont know what to do.

I have an excel spreadsheet, I need to create a formula that will indicate who is responsible for each request given the parameters that are set at the bottom. The responsibility will either be with IND or SCH depending on the date and time that the request was submitted...

Hi, I need to create a dynamic table that helps with backplanning from a specified date and time. It's meant to be used for project management, so I essentially something like this: If someone enters data into the blue boxes the full table should populate with dates and times in the format...

Hello, This may be very simple, I just can't figure it out. I need to chart some date & time info, it's 4 different things(times) per date. I can find a chart but have trouble changing the data field to my values or I change the values but don't like the chart. I am using the Office...