Time Format Only

[Billy Spivy]

Hello in my Time Sheet workbook there are cell values as:

2/13/2018 1:28:01 PM in the formula bar

Even though I set the format for Time (1:30 PM) as the choice in drop down.

It appears as 1:28 PM

I would prefer that in any occurrences of time they be 1:28 PM in the cell and in the formula bar.

Is there any way to ensure this happens ? I am trying to understand why they are different.

Thank you,
Billy

[kev_]

The formula bar shows the true value or a formula - somewhere we need to be able to see what is really going on.
What you see in the cell is determined by formatting choice - this does not have to bear any resemblance to the underlying value - with display settings it can display as virtually anything.

[Billy Spivy]

Thank you for replying. I have times on the 'Switchboard', 'Log', and 'EMP' sheets.

Is there a way to keep them consistent? There are codes to load the cells so maybe my issue is there.

I have tried to reformat all of the cells so they are the same but no success yet.

[kev_]

I am away from my PC until tomorrow and so cannot download your file.
(Unless it contains a formula) when you look in the formula bar it tells you what the underlying value is.
it is possible that some of your values are text rather than numeric.
If they are text rather than numeric, then you cannot make Excel display them the way you want - a date\time format only works if the underlying value is numeric.
If the underlying value is text, then the text must be converted to the correct number before it can be formatted as a date or time
You could investigate the cells that are not displaying the way you want.
The formula...

Please Login or Register  to view this content.

....returns TRUE if A1 contains a number and FALSE if it does not
So, if you apply the formula against the cells that are misbehaving it may point you in the right direction
If the formula returns FALSE you cannot format that value as a date or time

Another way to achieve the same thing is to format a cell as a number to 2 decimal places - if you do not see a number displayed, then the underlying value is text.

[Billy Spivy]

Kev,

I think I see what is going on. The code puts the date and time in cells and I prefer it to show like : 8:05:05 AM

Can this be done?

Please Login or Register  to view this content.

[davsth]

the value time is just the time

without understanding your code could you not get the time at the same time as clocktime, say put
justtime=time

as the line below in the vba code

then write justtime to the cell rather than clocktime

[kev_]

The code puts the date and time in cells and I prefer it to show like : 8:05:05 AM

The number format to display like that is:"h:mm:ss AM/PM"
Do you want the seconds displaying?

I am a little unsure what you are asking for
- the workbook you attached seems consistent
- is it the VBA that is doing something you don't like?
- I notice that the code is using function NOW which does include date and time
- formulas require the date to handle anyone working past midnight etc

this bit of code writes values to the sheet:

Please Login or Register  to view this content.

if you do not like what you are seeing

Please Login or Register  to view this content.

If you want anything else , please attach a workbook that ilustrates what you don't like - and make it obvious
thanks

[Billy Spivy]

Okay on the 'Switchboard' and 'Log' sheets the entries are filled by the codes.

In cell E9 on 'Switchboard' for example in the formula bar for that cell it shows:

2/13/2018 3:59:23 PM

I would like it to show:

3:59:23 PM

When clicking on cell J13 in the formula bar it shows:

1:30:00 PM (preferred)

Also when the 'Log' sheet is filled by the code I want the preferred.

Thank you so much for your help,
Billy

[davsth]

perhaps change the lines to

Please Login or Register  to view this content.

is that any closer?

[Billy Spivy]

That did it thank you Mr. Davsth.

The reason I wanted this changed is because I am manually entering times in the log for now to test out the calculations in the EMP sheets. I want to make sure they are accurate. When I manually enter it has a mix of code entries which had the date and time and the manual entries which only had the times. It was messing up the formulas.

I figure it is better with your code now so all entries of time in the workbook will be without the date included.

So much more consistent with your help thank you so much this has been frustrating but a valuable learning experience.

[davsth]

just as long as shifts don't go over midnight!

[Billy Spivy]

oh I didn't think of that but it probably will never happen.

I noticed though that I had to remove the *1440 and /1440 from the formulas in the EMP sheets.

[Billy Spivy]

I think I messed up again sorry can you check my formulas? On the EMP101 it isn't adding up the hours.

[Billy Spivy]

I have it figured out thanks again for your help.

[davsth]

glad to hear, I am not party to your logic in the time calculations, although they could be simplified!

what if the following was uses in i13 =IF(COUNTIF(E13:H13,""),"",MIN(F13,$H$5)-MAX(D13:E13)+MIN(H13)-MAX(G13,H6))

a few less ifs!

[Billy Spivy]

I am confusing myself and agree with your logic instead. Could you try it out and post revision? The flex times use different criteria because they don't rely on shift schedules.

I really appreciate it!

[Billy Spivy]

It does work however if an employee clocks in before lunch out time he should get paid until Lunch end from H6

[Billy Spivy]

I found it :

=IF(COUNTIF(E11:H11,""),"",MIN(F11,$H$5)-MAX(D11:E11)+MIN(H11)-MAX(G11,$H$6))

[davsth]

Well done! it was only a thought to make it slightly less messy. It made sense in my head, I did not want to confuse you!

[Billy Spivy]

How about the flex times have you come up with a formula? I will try

[Billy Spivy]

How about this does it work?

=IF(COUNTIF(E16:H16,""),"",(F16-E16)+(H16-G16))

[davsth]

it depends if a person can work a half day flexi and so not taKe lunch


if they can't it works

otherwise maybe something like
=IF(COUNTIF(E16:F16,""),0,F16-E16)+IF(COUNTIF(G16:H16,""),0,H16-G16)

[Billy Spivy]

Yours is obviously better thanks again! I hope I can return the favor(s) one day.

[Billy Spivy]

oh darn it. I have a crazy one here Suppose an employee is feeling sickly and doesn't follow their schedule but does do some work before going home early?

I attached and example in EMP102

[davsth]

Im not sure how the sheet is populated I assume they just press and get silly values in the afternoon

=IF(COUNTIF(E13:F13,""),"",MIN(F13,$H$5)-MAX(D13:E13))+IF(OR(COUNTIF(G13:H13,""),G13<0.5),0,MIN(H13)-MAX(G13,$H$6)) perhaps

[Billy Spivy]

It is populated by their clocking in or out on the 'Switchboard' sheet. It also populates the 'Log' sheet.

For now I am manually entering times in the 'Log' sheet for different scenarios.

[Billy Spivy]

That is very close but what if they don't work one day? I am getting #VALUE!

[davsth]

=IF(COUNTIF(E13:F13,""),0,MIN(F13,$H$5)-MAX(D13:E13))+IF(OR(COUNTIF(G13:H13,""),G13<0.5),0,MIN(H13)-MAX(G13,$H$6)) perhaps

[Billy Spivy]

Thank you I have been such a bother but do appreciate the help.

I will run this through the paces over a few days and try to come up with many scenarios to see if it breaks.

Enjoy the rest of your day sir

[davsth]

my day is nearly done, I'm off home in a few minutes. you have a good day. the formulas can be changed, but if you can express what you want in english, the formulas can be built or modified, enjoy your day