I have a datetime formatted as <day of month> <hour:minute:seconds> (I.e. 01 02:23:55) in general text. How do I separate day from rest and custom format as actual time stamp? TIA

like
Posting as :
works at
You are currently posting as works at

In general, you could use time functions to extract the time; like =HOUR(), =MINUTE(), =SECOND(). This way you could operate with the numbers.

Problem is, for excel 1 hour <> 1 (it’s actually 0.04, the same as 1/24). Do you need to display the hour as 01:45 PM, but still use that cell to calculate? (Can be done, I just don’t recommend)

like

Great suggestion! Thanks for the quick help!

like

I would do text to columns and play with the hour on its own column.

Related Posts

What's the SM1 salary at PwC AC in assurance line of service? Non Mba

Deloitte PwC

like

Can someone please explain to me the appeal of PBR? Or at least those of you with working taste buds. It’s more beer flavored water, no?

like

Hey Folks,

Can anyone refer me for SAP CS/SD consultant.
YOE : 2

like

Can someone explain 704(b) in simple terms?

like

Has anyone done Lupron and experienced significant hair loss? How long did it last? I feel like I’m going to be bald by the time the meds are out of my system 😢

like

What is Analyst.Automation Testing role in TCS? What are the typical responsibilities ?

I thought I was a Type A person, but the bench/holidays has taught me there's more to life than being 100% utilized and stressed out. New year, new priorities!

like

Hi! Any advice to a recent grad looking for a creative and media focused job? I’m hoping to work in something relating to creative marketing but am honestly unsure since most of my experience is at start ups and college orgs. Any advice and leads will be very appreciated

like

If anyone is interested in taking a summer trip to Myrtle Beach from 7/29-8/5, I am renting out my timeshare with Marriott's OceanWatch Villas at Grande Dunes. This resort is right on the beach and offers many fun activities for the whole family! The two-bed, two-bath condo has a fully furnished kitchen and can fit up to 8 people. I am ideally looking for someone to rent it out for the entire week for about 3K total. Message me for more information!

Post Photo

Best lunch around Truist park?

Is CQF useful to get entry level Quant job?? for non quant undergrad.

like

Is it possible that LTI gives offer without any variable component? Hr told me that my variable will be 0% .Larsen & Toubro Infotech LTI

like

I’m 9 days out from a desperately needed week-long vacation. I’m so unbelievably burned out that I can’t focus. I have so much to do before my trip, but I can’t seem to get anything done. I just sit at my desk and stare at my computer. Any recommendations for how to force myself to be productive for the next week or so?

like

Pwc AC Do you have any opportunities in life insurance QA I am open to relocate anywhere

like

where do people even buy fakes. i find it harder to buy a fake than a SS rolex

like

Tips for overcoming work stress while on the clock? I get so overwhelmed dealing with people all day

like

How much should I charge to design an instagram ad?

In case you missed it cringe, read today and reread yesterday because I want to post tomorrow that I had 200k reactions. Vurp.

Post Photo
funnylike

Hi. My team at Deloitte (India) is looking for people with experience in Threat Hunting & Splunk Administration.
If you have relevant experience, kindly drop me a message.

like

Additional Posts in Excel Genius

I have a table in sheet1. Column examples are ID, Initiative, Owner, etc. I’d like to use that table as a growing list of inputs. I’d like for sheet2 to be used by another team for other types of data inputs. But I need sheet2 to automatically populate the IDs from sheet1 as they change. Thoughts?

like

Does anyone know how to reformat a date from yyyy-mm-day to month-day—yyyy?

like

How can I make these dates sortable with a filter?

Post Photo
like

Coolest, best, or nerdiest thing you've been able to do with Excel?

like

Do you still use index match? Is it (Always) Possible to switch to xlookup?

like

Hello! I need to improve my excel skills. I know how to do some basic stuff but I need to do more. A client was teaching me how to use vlookup and I felt so useless! I decided to do something about it. I typed excel in udemy and there were so many results, I immediately became overwhelmed. Which one is the best? And shortest? Thank you!

like

Hey guys, I have a Google spreadsheet where each cell has multiple links separated by semi-colons.

How would you get the links to show up in different rows WITHIN a cell? (Pictured is what I want to achieve).

Thank you so much🙏

Post Photo
like

My Goal: I have a drop down menu in a cell. I want it to sort conditionally based on what I select. Is that possible?
Ex: If the drop down ="done" it falls to the bottom of the list.

like

What are your favorite excel macros to make you more efficient at work?

like

There has to be a way to restrict access on a worksheet so that only specific cells can be edited.

No matter how many times I’ve tried to automate and simplify generating reports to just inputting data into specific highlighted boxes, someone just have to try to edit a completely different cell then asking why the worksheet is “broken.”

likefunny

I have a table of different tickers of stocks and their according prices on different dates and times (hour and minute shown, no seconds). I'm trying to extract a singular price for each ticker based on date and time, but am having trouble doing so as there's multiple prices for each time (i.e. one row has $10 for 2:50pm, another has $11 for 2:50pm, etc) and was wondering if there was a way to pull only one price, let's say the highest, for a time?

like

Does anyone know how to keep in leading zeros when converting to a number, so I can do a vlookup across data sets?

like

Has anyone gone through Elevate Excel by Excel Campus? Looking for reviews as I'm having trouble finding any that aren't on their website....

like

Does anyone know why negative IRR sometimes gives you a NUM error? A lot of googling and experimenting but cannot pin down what’s going on.

like

What are the most typical models you all build

likehelpful

I’m trying to return a subset of two sheets. There is a list of names by week on one and a list of work assignments on the other. I want to filter out anyone who hasn’t done any of the assignments, but I want to return the full time period data for any employee who has worked on any of the assignments. Any suggestions? I wouldn’t mind being able to put this in PowerBI (answer could use Dax), and I’m a below average alteryx user but is another option. Thanks all!

like

So xlookup? How is it going for you? Still haven’t use it.

like

Help this is probably dumb. I have a workbook with 30 tabs. Is there a quick way to separate each in to its own book or should I copy one a time?

like

I have a spreadsheet that includes these columns: total AUM of firm X, % of AUM allocated by X to sector Y, % to sector Z, and so on (one column for each sector). Goal is to present in tabular form...

Need help with VBA. Have a folder of 40 files, I want to pull the 5th sheet from each file and combine it into one tab of an existing file. Can’t seem to get my code right...any suggestions fish??

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone

OR

Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal