How To Convert Cell Contents To A Number Using IF

[coltonyyz]

Hello!

If I have cell contents that represent a shift schedule, and I want to convert it to the number of hours it represents (seen below in bold numbers), how can I do that using IF?

Example of contents:

8:30 AM - 4:30 PM	8
2:00 PM - 4:00 PM	2
8:00 AM - 11:00 AM	3
11:00 AM - 4:00 PM	5
6:30 AM - 10:30 AM	4
8:45 AM - 2:45 PM	6
8:30 AM - 4:30 PM	8
8:00 AM - 3:00 PM	7

[AliGW]

Welcome tp the forum.

Why using IF? Are other solutions welcome? Is this TWO columns of data or just ONE?

[coltonyyz]

Oh, any method is welcomed! I figured IF would be easiest by simply creating another table using the formula. I'm all ears! I've updated my post to format it like a table to illustrate better what I need.

[sandy666]

with Power Query

Column1.1	Column1.2	Column2	Subtraction
08:30:00	16:30:00	8	8
02:00:00	16:00:00	2	14
08:00:00	11:00:00	3	3
11:00:00	16:00:00	5	5
06:30:00	10:30:00	4	4
08:45:00	14:45:00	6	6
08:30:00	16:30:00	8	8
08:00:00	15:00:00	7	7

Please Login or Register  to view this content.

yellow is your calculation, why 2 ?

[coltonyyz]

Thanks for pointing out the typo! It should have said PM - which would make the calculation 2.

With the programming code (I don't know what to call it, but the "Let Source =" part), where would I place this? Is this VBA?

[sandy666]

This is M-code , Power Query

https://docs.microsoft.com/en-us/power-bi/

would be fine if do that by yourself from the beginning using my code as example


btw

Column1		Column1.1	Column1.2	Subtraction
8:30 AM - 4:30 PM		08:30:00	16:30:00	8
2:00 AM - 4:00 PM		02:00:00	16:00:00	14
8:00 AM - 11:00 AM		08:00:00	11:00:00	3
11:00 AM - 4:00 PM		11:00:00	16:00:00	5
6:30 AM - 10:30 AM		06:30:00	10:30:00	4
8:45 AM - 2:45 PM*		08:45:00	14:45:00	6
8:30 AM - 4:30 PM		08:30:00	16:30:00	8
8:00 AM - 3:00 PM		08:00:00	15:00:00	7

[coltonyyz]

I've installed Power-bi but have no experience what-so-ever with it.

I have 190 columns of data with 37 rows for each. I was hoping I could simply do an IF statement to convert the cell contents to the number they represent. This is to calculate total hours, where the rows are employees, the columns are the days.

How did you paste your table in with that formatting so quickly? When I copy and paste from excel, it doesn't do any of the formatting, just straight text.

[sandy666]

Excel 365 contain Power Query by default so you no need Power BI

select your source table
Data tab
From Table
it will open Power Query window with your original table
the rest is in the code

if you want to use my code in PQ qindow find Advanced Editor then paste code there

btw, I did it with your example but not with 190 columns

[JEC.]

Try this one

Please Login or Register  to view this content.

[JEC.]

Or with a UDF

Please Login or Register  to view this content.

[coltonyyz]

Try this one

Please Login or Register  to view this content.

Thank you for this! As much as I appreciate sandy666's suggestion (and I did try to incorporate it into my spreadsheet, this reply I think will work easier for me!

I did get an error for line 9 on my uploaded sample worksheet. 8:45 AM - 2:45 PM gave me the #VALUE! error... any idea how this can be fixed so that it calculates the correct hours?

[JEC.]

There is a space behind 2:45 PM. Remove that first

[coltonyyz]

Thank you! Yes, this was the problem! And your formula I was able to copy it again for each 190 columns and it fill my table in, in its entirety! Thank you so much for this formula!!!

If a mod is reading this, I want to mark JEC's reply as the answer to my problem! I'm sure the other's would work too, but JEC's was the easiest; for me at least!