time format

  1. A Durfani

    Weird Time Format

    Below is a Time format I copied from our company's system in cell first cell B2 Time is 102 hours and 30 minutes which is correct but when I see in formula bar it is showing 1/4/1900 6:30:00 AM which is not correct I want to extract hours and minutes like 102:30 (102 hours and 30 minutes) and...
  2. sakis_s

    Cells in time format. Trying to find a way to copy them the way i need.

    Hi! I'm trying to figure out how can i copy a cell that is in time format the way i need. I've tried different things but nothing works the way i want and i run out of ideas so i'm kindly asking if you have any other ideas to make it work. My example: In sheet1 A1 field is in "Time" format and...
  3. P

    Format Painter Doesn't Work

    (Using both the latest Excel release (2019?) and libraries with legacy releases) I have some music track time lengths that are in the format 5:43 (h:mm), but another column displays 5:43:00 AM. So I used both the Format, Number, Custom, h:mm option on the cells that showed the AM designation...
  4. K

    Data Validation - 15 Minute Intervals - Time Range

    I have a column of cells in which I need users to input their time spent on a project ( time input as [hh]:mm ) the hours are accumulated over a 6 month period so can exceed 24 hours - but I would like the times entered to be restricted to 15 minute intervals (eg. 60:00, 55:15, 43:30, 38:45...
  5. P

    Convert hh:mm:dd to decimal time

    Hello, I've tried converting the format hh:mm:dd to decimal time and it isn't working. My input data values are coming from a SW program that creates a text output. It is coming in either as general or custom format. Input = 76:41:30 I want my output to be 76.69 I think my problem may lie...
  6. T

    VBA macro - change time format and select rows with specific month

    Hi guys, I have a big file with columns D & E that contains time of starting an action (D) and finishing the action (E). I need the time in a custom format, like: mmmm dd, yyyy hh:mm:ss AM/PM Records are from the last 2 years, but i'm interested in a specific month, let's say, January 2017 and...
  7. A

    Comparing Times in Access

    I'm working on an employee schedule. I have folks start and end times as well as break and lunch times. I'm trying to create 1/0 for working/not working in 15-minute intervals throughout the day. My equation looks to say if the interval is less that the person's start time, greater than the...
  8. S

    Formatting more than a months worth of seconds

    First, let me clarify that I am NOT trying to "convert" seconds to a "# of days, #of hrs, # of mins" ... I am trying to take a cell that has a number of seconds already in it, but I would like it "displayed (formatted) to show "# of days, #of hrs, # of mins". I'm struggling with finding a...
  9. P

    Copy time formatted cell

    Hi Folks I'm trying to join the content of two cells: one of them is in the format 10:40:15, and the other is in the format 11/11/2015. When I try to concatenate the contents of both cells to get something like "11/11/2015 10:40:15", the date field format goes ok, but the one with the time...
  10. M

    Text to Columns Time Issue in Macro

    I have data that is formatted like so... <tbody> calldate 12/12/2015 18:52 12/12/2015 16:50 12/12/2015 16:17 12/12/2015 14:00 12/12/2015 13:44 12/12/2015 13:35 12/12/2015 13:11 </tbody> I'm running a macro, and one part of that macro is to separate the date/time column into...
  11. J

    Time conversion

    Hi there gurus! I'm trying to convert a general entry into a cell into a time format. The general entries are all entered as 06:00am for example. I've tried changing the formatting of the cells but as the data is pulled from a website it continually enters refreshed data as a general format...
  12. Prevost

    check cell value to given number of significant digits

    Hi There. I want to check a cell value to a given number of significant digits. Is there a way to do this using a function? Say if I wanted to check if the value of a cell is pi, but only to two significant digits (so 3.14) is there a function or vba that I can use to check a number and cut off...
  13. Prevost

    Checking to see if time difference is 1 hour using if function

    Hi There. I want to use an if function to determine whether or not the time difference between 2 cells is not equal to 1 hour. I tried =if((A2-A1)=0.0416666666666667, 0,1) but it does not work all the time. For example, 16:00-15:00 is not 0.0416666666666667. I converted the time difference of...
  14. T

    Help me convert a column of times from text, but starting like this (thank you)

    <colgroup><col style="width:48pt" width="64"> </colgroup><tbody> 754 1007 1102 806 </tbody> I've hand tallied a bunch of data (as text format), but to save time, I didn't enter the ":" (notice I didn't say "enter the colon"...wait I just did:warning:)...
  15. B

    Calculation of breaks between workshifts

    Guys please help as I ran out of ideas: I need to calculate workers' rest time between two shifts, example Day 1 Time in: 8:00 Day 1 Time out: 23:00 Day 2 Time in: 8:00 Day 2 Time out: 17:00 Rest between shifts: 9hrs [so formula would be Day 1 Time out - Day 2 Time in, but it never returns...
  16. L

    Problem with export query for time formatted data from Access 2010 to Excel 2010

    Hi, I have an excel .xlsm spreadsheet used for several years that has many ms query connections to an access db. After the company upgraded to Win 7 \ Office 2010 one of the simplest ms queries will not work. This query simply goes out to an access db table which contains one record and 1 field...
  17. Norm Shea

    Formatting a number string to "time"

    I have a mapping program that uploads data with the date and time but when it exports it the time is converted to just a number string, e.g. 15:12:37 becomes 151237000. Is there way to eliminate the last three zeros and insert a ":" between the second and third, and fourth and fifth characters?
  18. T

    Time format setup

    Hi, I got a big database of phone calls and I'd like to analyze call duration. 000:00:02:10 --- this is the format of the numbers I'm getting, this should be 2 minutes 10 seconds, but I can't really transform it to the right format to be able to use it for creating sums or averages. Any...
  19. S

    Time Format

    I have time in format 7:00 (without AM/PM). How can I format it to proper time format 7:00 AM/PM. I am using excel 2007
  20. B

    Time cell does not add correctly

    Good morning all, I am trying to add the values in cell H73 (see below), but what I get is not correct. In this cell H73, formatted as Time, I have this very simple formula: =H14+H28+H42+H56+H70 <TBODY> G H 68 Total Hrs. worked 9:00 69 Total min. Travel Time 1:00 70 Daily total 10:00...

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
Top