• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Converting time formats|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Converting time formats|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Converting time formats
sp_PrintTopic sp_TopicIcon
Converting time formats
Avatar
Loren Killgore
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 30, 2022
sp_UserOfflineSmall Offline
1
October 27, 2022 - 9:54 am
sp_Permalink sp_Print sp_EditHistory

I have a report that I export into Excel (see attached).  It comes from Global Shop Solutions > Crystal Reports Viewer and is a report on time spent in work centers.  I've cleaned and organized the data, now I just need to translate and calculate accurately.

You'll notice in the attached file, columns I and J are in decimal format.  In columns M and N, I've manually typed in some examples what it should be.  I used find and replace (period to colon with Ablebits Tools), then I used the [h]:mm formatting tool.  That worked for most of them.  But inevitably, I still get some values that translate incorrectly.  Most notably, even values, such as those in row 99 (20.00 hours, zero minutes, etc) would translate over as 480:00. (It must be thinking 20*24 hours somehow?)

I have nearly 200,000 rows of data.  Does anyone know of a method for doing this quickly and accurately?  For reference, if we add up all the Est hours and Act hours for the 5X01 W.C. (column A), they should total 8,366:14 and 4,114:42 respectively.

Thank you in advance!

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
2
October 27, 2022 - 3:51 pm
sp_Permalink sp_Print

Hi Loren,

Enter this formula in M2 to transform the estimated hours:

=INT(I2)/24+MOD(I2,1)*100/1440

Copy it all the way down and one column to the right and you'll get the hh:mm values you need. Custom format both columns M and N as [h]:mm

Riny

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 617
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
3
October 27, 2022 - 11:55 pm
sp_Permalink sp_Print

You could also use:

=TEXT(I2*100,"00\:00")+0

with the same [h]:mm formatting

Avatar
Loren Killgore
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 30, 2022
sp_UserOfflineSmall Offline
4
October 28, 2022 - 7:52 am
sp_Permalink sp_Print

Riny: Everything calculates and no errors were returned.  Thank you!  However, when there is a negative time value, it calculates incorrectly.

Velouria: I get errors when there is a negative time.

In the original file, I had to scroll down to row 649 before I found some negatives.  It's rare but sometimes a manager has to manually subtract time from worker's time card.  So that's when it shows up as a negative value.

I tried putting a negative sign in the formulas, but that didn't work.  Other than negative numberScreenshot-2022-10-27-145132.jpgImage Enlarger

s, it's working great!  One step closer!

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot-2022-10-27-145132.jpg (114 KB)
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
5
October 28, 2022 - 3:17 pm
sp_Permalink sp_Print

Yeah, forgot to mention that. Noticed your data had one negative in it. Assumed that is was a mistake of some sort, as there really isn't something like "negative time". Looking at your screen shot I can only wonder how something estimated not to take place (i.e zero seconds) will actually take minus 1 second.

Can you clarify that?

You can set-up Excel to use the 1904 date system. It allows you to display negative numbers in a time format. Though, it shifts all your dates by 4 years.

Avatar
Loren Killgore
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 30, 2022
sp_UserOfflineSmall Offline
6
October 29, 2022 - 12:27 am
sp_Permalink sp_Print

I can try to clarify.  It will likely be a long phone call with Global Shop Solutions (GSS) .  (Quick fact: I'm still elementary in my data analyst roll.  But those are the exact people I talk to at GSS - data analysts and scientists.  Trying to figure out a lot of their processes!)

My only thought on the negative time right now is that a manager has to go in sometimes and manually adjust the employee's time card, due to clocking in/out of the wrong job or work center.

I will look into the 1904 date system.  Thank you!

Avatar
Loren Killgore
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 30, 2022
sp_UserOfflineSmall Offline
7
January 17, 2023 - 3:49 am
sp_Permalink sp_Print

I've run into a problem with this again. Maybe it was happening all along and I didn't realize it. (Though I sure hope not!!) All of sudden, I've noticed that it's just not converting the numbers as I desire.

To reiterate: I export from Global Shop Crystal Reports Viewer to Excel. Right off the bat, I have a problem of it turning a colon into a decimal. The steps above helped resolve that and it's what I've been doing for a while. Up until now.

You'll see in the attached file, ROW 10 is a good example of the way things should be. (Looking primarily at columns H, I, K and L, Estimated & Actual hours, and their respective conversions in K and L). In row 10, it takes 7.20 and converts it to 7:20 (seven hours & 20 minutes; also converts 3.25 to 3:25 correctly) Rows 4 and 14 are also correct.

Alas, most of them are NOT converting correctly. This totally caught me off guard. I double checked my cell format [h]:mm and that looks good. I double checked everything mentioned above and it is also correct so far as I can tell.

Any suggestions????

Avatar
Loren Killgore
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 30, 2022
sp_UserOfflineSmall Offline
8
January 17, 2023 - 3:50 am
sp_Permalink sp_Print sp_EditHistory

Here is my attachment again in case it didn't come through.

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
9
January 18, 2023 - 4:38 pm
sp_Permalink sp_Print

Didn't see you latest message until now.

Perhaps you can start by clarifying again what we are looking at. On Row 10 you say 7.20 and 3.25 correctly translates to 7hrs 20min and 3hrs 25min. Following that principle, I would say that most conversions are correct. But, perhaps where you get unwanted results is where the decimal part is greater than 0.59.

And the fact this this is possible makes me believe that we are looking at decimal representations of duration. For example, row 3, 0.9hrs should translate to 54 minutes. With the formula that you have used so far, 0.9 translates to 0hrs 90min (i.e. 1hr 30min).

Similar for row 6: 15.60 translates to 15hrs 60min equaling 16hrs. But perhaps this should be 15hrs 30min.

If we are looking at decimal hours, 7.20 should be 7hrs 12min (i.e. 7.2/24 = 0.3, formatted as [h]:mm ).

I can't tell what's correct. You?

Avatar
Loren Killgore
Member
Members
Level 0
Forum Posts: 10
Member Since:
August 30, 2022
sp_UserOfflineSmall Offline
10
January 19, 2023 - 3:20 am
sp_Permalink sp_Print sp_EditHistory

I just followed technology 101 and did a fresh re-export of the data. I then spent 10 minutes simply looking and comparing numbers.

Looks like it's all back to good again!

My only guess is the data got corrupted somewhere along the way. Maybe during a copy/paste or something.

Okay to disregard this latest hiccup! 🙂

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ahmad Alkhuffash, Chandler Davis, Ramon Lagos, michael serna
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6215
Posts: 27245

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.