time and date

  1. L

    Adding Hours Based on Time Zone

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

    How to get the same data time in multiple time series?

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

    Convert Cumulative Time / Time Duration to Text or String

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

    How to get GMT time on MAC with vba.

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

    Time formula with conditions

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

    Copy date ranges to new worksheets retaining all data in the original worksheet

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

    Calculate dates and times for regular time & overtime ...and also consider weekends and holidays

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

    Calculating hours/minutes between time ranges

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

    Using MODE with Time and Days of the Week?

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

    Pairing historic time with current time

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

    Strip date from date/time cell, format correctly so it's recognised as a date by Excel?

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

    Calculate meal allowances

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

    Macro Finding First Empty Cell In Column Then Returning Date OR Time

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

    Excel Time and Date issues

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

    IF Formula with date and time parameters

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

    Adding and subtracting time and date within a specified date range

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

    Charting date with multiple times

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

    Converting duration in NUMBER format to duration in TIME format in Excel.

    Dear all, I have the duration data for more than 40000 rows in the number format. I need a method to convert the same into time format in excel. BY saying number format i mean 1 Hours & 45 minutes has been given as 1.45. I would like to have the same value 1.45 in the TIME format in excel so...
  19. M

    Calculating Cell value hours from Now()

    Hi all, Long time luker now hoping for some help. please. We wish to maintain a running log of prodcution scheduling. The format we have been attempting is (E1)Print Date & Time (F1)9/21/2016 (G1)4:55:36 PM Job Number Part Number Description...
  20. J

    using time and date data to chart trends

    We have time and date data from our ordering system, we want to be able to see the trends in which day of the week/time of day are the most common for orders being placed. This is what our data looks like (copied out of excel, formatting in excel is two columns, Name and Created date, date and...

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