Cell formatting - Duration between time

Question

I am trying to make a time sheet as 'untouchable' as possible by using formulas. The business used to use a timesheet software, but they are now using excel as they are required to calculate overtime, weekend hours, food allowance ETC ETC. 

Currently, they are having to calculate the time between clocked on an clocked off from an exported timesheet as they require the minute value to turn it into H:MM (i think we can remove the minute step). They have been using a website to do this, and it takes a ridiculous amount of time! 

I am having trouble with the cell formatting of the time entries. They come through as hh:mm:ss which i assumed would be perfect, however excel isn't picking up on this format. We have to copy over the time to an excel template of ours with all the other formulas (See below)

The format of the cell is General, and even when i click to change it to Custom > h:mm:ss nothing changes which means my formula  =("end time"-"start time"+("end time"<"start time"))*1440) does not work as it needs the time format.

Now, the simple fix is to just re-write the start or end time. When i do this, it works perfectly, however with so many employees in the one week, i would be adjusting over 100 numbers! (See below when typing in clocked time) 

I really have no idea what to do, so suggestions are encouraged and appreciated.

Answer 1

So, i thought had solved the problem. The data was ever changing when it came in. There were 2 ways the time came in, manual input and the input time. 

Manual import worked fine, as excel recognized 6:30 = 6:30:00 AM 

No problems there

However, when the time came though as it is meant to (e.g. 15:48:39) it wasn't working because.... there was a space in front of it. Easy fix. I just used the RIGHT formula to cut out the space and return the cell to time format. 

So, the formatting is fine... the problem is working with the both of them TOGETHER. 

Here is my current formulas :

Start time calculation =IF((OR($G3="",G3=0)),0,RIGHT(C3,8))

End time calculation =IF((OR($G3="",G3=0)),0,RIGHT($E3,8))

Total per day calculation=IF((OR(I3=0,J3=0)),0,(J3-I3+(J3<I3)))

Minutes converted to hours =IF((AND(I5<>0,J5=0)),(E5-I5)*24,IF((AND(I5=0,J5<>0)),(J5-C5)*24,IF((AND(I5=0,J5=0)),(E5-C5)*24,K5*24)))

And the excel below:

So it is mostly working except when i get a mixture of the 2 formats!!!!!

I know what i am missing. E5 needs to have a RIGHT formula applied to it, i just don't know how to fit that in! As my logical test for my IF OR formula in column J  (end time) only applies if column G is empty or 0, it means the amount is returned to zero. 

I think getting this RIGHT formula in somewhere is what i need... but i just don't know where to fit it in...

Any help is really really really appreciated.

And no macros plz

Answer 2

What you are doing is generally known as "Data cleaning" or "data cleansing". Getting data from any source and changing it into the format you need

Here area a couple of date tips, but I'm willing ot bet you could adapt the technique for time also

Date- How to Convert Text to Dates [Data Cleanup] http://chandoo.org/wp/2010/03/23/text-to-date-c...

Sometimes when we import data from another source in to excel, the dates are not imported properly. This can be due to any number of reasons. In this post, we will learn some tricks and ideas you can use to quickly convert text to dates.

Text to Columns to Correct Date Formats https://www.myonlinetraininghub.com/excel-text-...

If you work in Excel with data imported from other databases you’ll often find it doesn’t import it in the format you want. For example, I imported some Google Analytics data about our website traffic and the dates are formatted like this: Tuesday, February 1, 2011 While this looks like a date format, in my worksheet it is actually text, and if I try to apply a different date format using the Format Cells > Number > Date options, Excel does nothing. To re-jig the dates we need to do a few steps. We’ll separate the data in column A (where our dates are) into 3 columns; month, day and year. And we'll get rid of the day name.

***********************************************

But in general, the tool you want to use is called "Get & Transform" in 2016, "Power Query" in 2013. Rather than creating a macro, you do your "transformation" while Excel records what you do. Later you can import more data and excel will re-apply the transforms (like a macro).

Here is a good article on it:

Get & Transform- An Alternative to Reformat Macros http://www.excel-university.com/get-transform-a... June 16, 2016 Jeff Lenning

Here is the scenario. We export data out of some system, and save it in a CSV file. We then need to prepare it for use. Perhaps to import into another system, or perhaps to use in a PivotTable or formula-based report. We basically need to clean up the data, remove some columns, change the headers, and so on.

Back in the old days, we could automate such a task with a VBA macro that reformatted the data. Yes, it was difficult to write initially, but, it felt great when we got it working. And life was good, until something in the data changed….like a new column. Some type of change like this could break the macro. Then, we would need to crack open the Visual Basic Editor and troubleshoot. It took some time to figure out how to resolve the issue, but, it felt great once we got it working again. Until something else changed. Argh.

The good news is that a Get & Transform Query is an easy alternative to building such a VBA macro. Best of all, modifications are easy to make when something changes.

*******************

Getting Started with Get & Transform in Excel 2016 https://support.office.com/en-us/article/Gettin...

With Get & Transform in Excel 2016, you can search for data sources, make connections, and then shape that data (for example remove a column, change a data type, or merge tables) in ways that meet your needs. Once you’ve shaped your data, you can share your findings or use your query to create reports.

******************

As well, if you want to see it in use take a look at the the Power Query webinar replay in the following links

@ Power Query and PowerPivot Definitive Guide http://www.myonlinetraininghub.com/power-query-...

You can catch a replay on the Excel webinars page ( http://www.myonlinetraininghub.com/excel-webinars ). Scroll down to the second webinar which includes Power Query and PowerPivot.

It was great to see the excitement around Power Query and PowerPivot, but this meant there were lots of questions about these tools, so I’ll attempt to answer them here.

********************

Here is another free webinar recording demonstrating Get and Transform

@ MyExcelOnline 2 Pivot table webinars by John Michaloudis and Oz Du Soleil

Free Power Query & Data Cleansing Webinar https://www.myexcelonline.com/free-excel-webinars

* The most powerful feature in Excel since VLOOKUP...it's called POWER QUERY (GET & TRANSFORM) & it will save you HOURS each day!!!

* Transform messy data & automate your daily/weekly/monthly reports within minutes (NOT DAYS!)... AND without using VBA or Macros!

* Data Cleansing Excel Tips & Special Bonus attendee material that you can keep and use to become better at Excel straight away, skyrocketing your personal & professional development!

* Plus learn how to use Data Models, Unpivot, Parse Text, Consolidate Reports & Flash Fill!

* Data Models- Analyzing multiple datasets as if they are a single dataset

* Parsing Text- Splitting data apart, ie when name aage and email are all in same cell

* Inner Join – Comparing lists to extract data that appears on both lists

* Unpivot – convert a report into raw data so that I is useful for analysis

* Consolidate multiple worksheets

Answer 3

Do you want to me to correct your dates also? The single macro can take care of both date and time.

Answer 4

There is no other option but to use a macro to convert this as your time have come from website and they do contain other invisible characters.

What I need a portion of your workbook which has few entries of your time so that I can test the macro.

Request you to please prepare and upload a sample / dummy file sans confidential / sensitive data to Onedrive and share the link? It will help me to give prompt and high quality solution.